checking existance in a list

[rcuser]

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}

[lilleman]

Hi,

A query like the one below should do the trick:

SELECT myID FROM myTable
  WHERE myID NOT IN(2,6,3)

[r937]

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

[rcuser]

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.

[lilleman]

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.

[rcuser]

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

[lilleman]

Hi,

Would this pick up myID=2 as being in the list or not?

The answer is no, it will not pick out myID=2.

[rcuser]

Great, Thanks for your help.

Richard

[asterix]

In MySQL, the following query will select all of the values in the myID field,

  SELECT myID FROM myTable
    WHERE myID <> ANY (22,6,3)

as long as those values are not in the list above.

[r937]

In MySQL, the following query will select ...

not in mysql 4.0 it won't

[asterix]

not in mysql 4.0 it won't

What is their problem at MySQL? First they come up with brain dead syntax (don't even get me started on their multi-table update syntax) but they don't even make it backwards compatible!

That really annoys me, first bringing out truly odd syntax. Then not supporting it.

[r937]

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???????????????

[r937]

maybe you were thinking of ANY (subquery)

http://dev.mysql.com/doc/mysql/en/an...ubqueries.html

[asterix]

maybe you were thinking of ANY (subquery)

Yep... My mistake.

I don't have mySQL 4.0 either.
What about this?

  SELECT myID FROM myTable
	WHERE myID <> ANY (select myid from mytable where myid in (1,2,3) )

or

  SELECT myID FROM myTable
 	WHERE myID <> 1 and myid <>2 and myid <>3

[r937]

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

[asterix]

yeah, stupid me. Lilleman already posted the solution:

http://www.sitepoint.com/forums/show...96&postcount=2