| SELECT records NOT in another table
Help creating a query |
RandallK
msg:4107104 | 5:27 pm on Mar 30, 2010 (gmt 0) |
I've got two tables. Table A is Fruit, Table B is Vegetables.
Table A
-----------
id|name
1|orange
2|apple
3|grape
4|tomato
Table B
-----------
id|name
5|carrot
6|cucumber
4|tomato
I want to select all the items from table B, that are NOT in table A? What is the best way to accomplish this?
|
LifeinAsia
msg:4107108 | 5:32 pm on Mar 30, 2010 (gmt 0) |
You could use a LEFT OUTER JOIN:
SELECT TableB.id,TableB.name
FROM TableB LEFT OUTER JOIN TableA ON TableB.id=TableA.id
WHERE TableA.id IS NULL
|
RandallK
msg:4107117 | 5:39 pm on Mar 30, 2010 (gmt 0) |
Beautiful.
|
syber
msg:4107955 | 1:29 am on Apr 1, 2010 (gmt 0) |
another way is to use NOT EXITS:
SELECT id, name
FROM TableB
WHERE NOT EXITS (SELECT * FROM TableA
WHERE id=TableB.id)
|
RandallK
msg:4108588 | 10:06 pm on Apr 1, 2010 (gmt 0) |
Thanks for the tip. Worked well also.
For anyone who might come across this in the future... please note it is NOT EXISTS. Took me a minute to figure out why it wasn't working...
|
|
|
