Let me rewrite what it's meant to do as i mgiht not have been clear. ok for a single user I want to get all the users (from the user table) that aren't a buddy for that user.
users 1 bob 2 tom 3 mike buddylist 1 2 1 3 2 1 2 3 3 1 //therefore if i searched for mike it would return tom as he is not listed as a buddy for mike but bob is. The query is half working but doesnt seem to be working for an individual user. thanks to those who have helped so far... Michael Stassen wrote: > 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]
