Welcome to WebmasterWorld Guest from 54.166.220.138

Forum Moderators: open

Message Too Old, No Replies

SELECT records NOT in another table

Help creating a query

     
5:27 pm on Mar 30, 2010 (gmt 0)

5+ Year Member



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?
5:32 pm on Mar 30, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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
5:39 pm on Mar 30, 2010 (gmt 0)

5+ Year Member



Beautiful.
1:29 am on Apr 1, 2010 (gmt 0)

10+ Year Member



another way is to use NOT EXITS:

SELECT id, name
FROM TableB
WHERE NOT EXITS (SELECT * FROM TableA
WHERE id=TableB.id)
10:06 pm on Apr 1, 2010 (gmt 0)

5+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month