nested selects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

nested selects

Author

Topic

Xenlpe
Starting Member

8 Posts

Posted - 2001-12-12 : 10:44:11

Hi,
I have a problem, and is:
On a single table, myFriends, with three columns (ID, Name, Chosen) where I have a simple relationship on my friends names. If John hasn't chosen, he has a 0 in [myFriends.Chosen] but if he has already chosen, here is the [myFriends.ID] of the person whom he's selected.
What I am trying to do, but haven't been able to is to have a select, is to get all my friends who haven't been chosen (myFriends.Chosen = 0) minus my friends who had chosen somebody. So if we have:

 
ID  Name       Chosen
-----------------------
1   Charles    0
2   Anna       4
3   Julius     1
4   Rosie      0
5   Mann       0
6   Robert     0

I want to get as result only Mann and Robert, because the others have already, either chosen somebody or have been chosen (Rosie by Anna and Charles by Julius)
Please Heeeelp!

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-12-12 : 10:55:32

select * from myfriends as m
where choosen = 0
and id not in ( select choosen from myfriends)