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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
