SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
Thread: checking existance in a list
-
Feb 18, 2005, 09:59 #1
- Join Date
- Jan 2004
- Location
- London
- Posts
- 87
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
checking existance in a list
Is there a list facility in SQL. I'm trying to look for ID numbers that are not in a list.
i.e.
select myID
from myTable
where myID {doesn't exist in this list}
-
Feb 18, 2005, 10:02 #2
- Join Date
- Feb 2004
- Location
- Örebro, Sweden
- Posts
- 2,716
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi,
A query like the one below should do the trick:
Code:SELECT myID FROM myTable WHERE myID NOT IN(2,6,3)
ERIK RIKLUND :: Yes, I've been gone quite a while.
-
Feb 18, 2005, 11:06 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
here's a tip: never write ... WHERE NOT id IN ( 2,6,3 )
this works exactly as expected in other databases, but not (no pun intended) in mysql
mysql has some weird logic, and this example is amongst the worst
-
Feb 21, 2005, 05:05 #4
- Join Date
- Jan 2004
- Location
- London
- Posts
- 87
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks for getting back to me.
I've a quick question, if my id=2 would it be counted in the list if there was a value of 22,?
I'm hoping not but I've come across this problem before with other programming.
-
Feb 21, 2005, 05:26 #5
- Join Date
- Feb 2004
- Location
- Örebro, Sweden
- Posts
- 2,716
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi,
What the IN function does is to check if the specified value (in this case, the value of a the column called myID) exists in a comma-separated list. You could compare this to using in_array in PHP.ERIK RIKLUND :: Yes, I've been gone quite a while.
-
Feb 21, 2005, 06:43 #6
- Join Date
- Jan 2004
- Location
- London
- Posts
- 87
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sorry you've lost me I don't use PHP.
Would:
SELECT myID FROM myTable
WHERE myID NOT IN(22,6,3)
Would this pick up myID=2 as being in the list or not?
Thanks,
Richard
-
Feb 21, 2005, 06:47 #7
- Join Date
- Feb 2004
- Location
- Örebro, Sweden
- Posts
- 2,716
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi,
Originally Posted by rcuser
ERIK RIKLUND :: Yes, I've been gone quite a while.
-
Feb 21, 2005, 07:20 #8
- Join Date
- Jan 2004
- Location
- London
- Posts
- 87
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Great, Thanks for your help.
Richard
-
Feb 21, 2005, 08:36 #9
In MySQL, the following query will select all of the values in the myID field,
Code:SELECT myID FROM myTable WHERE myID <> ANY (22,6,3)
-
Feb 21, 2005, 08:39 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by asterix
-
Feb 21, 2005, 08:53 #11not in mysql 4.0 it won't
That really annoys me, first bringing out truly odd syntax. Then not supporting it.
-
Feb 21, 2005, 09:00 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
are you saying this does work in a release later than 4.0?
i can't test it, because i can't install anything myself
and on which planet does any software vendor introduce a new feature in a new release and then go back and make it work in all previous versions???????????????
-
Feb 21, 2005, 09:04 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
maybe you were thinking of ANY (subquery)
http://dev.mysql.com/doc/mysql/en/an...ubqueries.html
-
Feb 21, 2005, 09:43 #14maybe you were thinking of ANY (subquery)
Yep... My mistake.
I don't have mySQL 4.0 either.
What about this?
Code:SELECT myID FROM myTable WHERE myID <> ANY (select myid from mytable where myid in (1,2,3) )
Code:SELECT myID FROM myTable WHERE myID <> 1 and myid <>2 and myid <>3
-
Feb 21, 2005, 09:55 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
well your first one won't work in 4.0 because it uses a subquery
and your second one works but is needlessly convoluted when a simple IN list is so much simpler
-
Feb 21, 2005, 10:02 #16
yeah, stupid me. Lilleman already posted the solution:
http://www.sitepoint.com/forums/show...96&postcount=2
Bookmarks