[RESOLVED] One to Many SQL statement (subquery)

[Zoila]

I would like to get the name of each buddy I have listed in my tblBuddies table from a subquery.

Select userName From tblIMUser where userID = (Select distinct userbuddy From tblBuddies Where userID = 4)

From this I get a subquery error Any ideas on how to get around this I was thinking of a for Each Loop in the SQL
what do you think

tblIMUser
userID (PK) userName
----------------------
1 Name1
2 Name 2
3 Name 3
tblBuddies
UserID BuddyID
----------------------
1 - 2
1 - 3
3 - 1
3 - 2

[Shuja Ali]

First of all when you use Subquery you are not supposed to = sign. Instead use the In .

And why not use Join in this case.

[Zoila]

good questions my cells aren't working today I suppose : )))

Thanks worked great ))

[szlamany]

Never loop - always get the query right!

SQL is all about set-based logic!

Select tblIMUser.UserName From tblIMUser
Left Join tblBuddies on tblBuddies.BuddyId=tblIMUser.UserId
Where tblBuddies.UserId=4
Group by tblIMUser.UserName