homepage Welcome to WebmasterWorld Guest from 54.211.68.132
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved