Home » SQL & PL/SQL » SQL & PL/SQL » Using WHERE to SELECT when records are contained in another table
Using WHERE to SELECT when records are contained in another table [message #249186] Tue, 03 July 2007 10:44 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I have tried and researched this forever.

I have two tables.

USER_IDS and PURCHASES

I want to run a query the will select ID FROM the USER_IDS table when it is contained in the purchases table.

Here's what i've been trying:

SELECT ID FROM USER_IDS WHERE ID = (SELECT ID FROM PURCHASES)
Re: Using WHERE to SELECT when records are contained in another table [message #249192 is a reply to message #249186] Tue, 03 July 2007 10:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member

SELECT ID FROM USER_IDS WHERE EXISTS (SELECT ID FROM PURCHASES)
Re: Using WHERE to SELECT when records are contained in another table [message #249194 is a reply to message #249192] Tue, 03 July 2007 10:51 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Nice, does them mean there is a NOT EXISTS? Or would you just adjust it according to the WHERE query?
Re: Using WHERE to SELECT when records are contained in another table [message #249198 is a reply to message #249192] Tue, 03 July 2007 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Marc, you missed the join condition between outer and subquery:
SELECT ID FROM USER_IDS WHERE EXISTS (SELECT ID FROM PURCHASES where PURCHASES.id = USER_IDS.id)


hdogg, yes there is also a NOT EXISTS operator (like NOT any other one).
You can also use your first query with IN:
SELECT ID FROM USER_IDS WHERE ID IN (SELECT ID FROM PURCHASES)


Regards
Michel

[Updated on: Tue, 03 July 2007 11:00]

Report message to a moderator

Re: Using WHERE to SELECT when records are contained in another table [message #249209 is a reply to message #249186] Tue, 03 July 2007 11:18 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
details details, who needs a join lol

Nice Catch Michel

Can you tell it's almost a holiday here in the U.S.
Previous Topic: SQL doubt
Next Topic: PURITY LEVELS
Goto Forum:
  


Current Time: Mon Dec 05 15:00:08 CST 2016

Total time taken to generate the page: 0.05194 seconds