Angelo Zanetti wrote:
> thanks shawn it seems to be working but i forgot to add that i need it
> for a single user ID, in other words it must bring back all user ids in
> the user table if they do not exist for that user in the buddylist.
>
> so what i've tried is this:
>
> SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID
> WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null
>
> but this still returns some results that exist in the buddyList table.
I'm not sure Shawn's query does what you originally asked. His was
SELECT u.UserID
FROM Users u
LEFT JOIN BuddyList bl
ON u.userID = bl.userID
WHERE u.isactive =1
AND bl.userID is null;
which returns active users with no buddies. As I understood it, you
wanted active users who aren't on another user's buddy list. I believe
that would require joining on buddyId rather than userID, like this:
SELECT u.UserID
FROM Users u
LEFT JOIN BuddyList bl
ON u.userID = bl.buddyID
WHERE u.isactive =1
AND bl.userID is null;
Adding your new requirement that we only look at a single user's buddy
list should be simple:
SELECT u.UserID
FROM Users u
LEFT JOIN BuddyList bl
ON u.userID = bl.buddyID AND bl.userID = '$userid'
WHERE u.isactive =1
AND bl.userID is null;
Does that do what you want?
Michael
