List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 8 2005 5:04pm
Subject:Re: LEFT JOIN?
View as plain text  
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
Thread
LEFT JOIN?Angelo Zanetti8 Jun
  • Re: LEFT JOIN?SGreen8 Jun
    • Re: LEFT JOIN?Angelo Zanetti8 Jun
      • Re: LEFT JOIN?Michael Stassen8 Jun
        • Re: LEFT JOIN?Angelo Zanetti8 Jun
          • Re: LEFT JOIN?SGreen8 Jun
  • Re: LEFT JOIN?Alec.Cawley8 Jun
Re: LEFT JOIN?Michael Stassen8 Jun
  • Re: [SOLVED]LEFT JOIN?Angelo Zanetti9 Jun
    • Enhance a queryHarish Gabbita27 Jun
      • Re: Enhance a querySGreen27 Jun