List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 8 2005 6:30pm
Subject:Re: LEFT JOIN?
In-reply-to:
<OF069A8636.A624C4AD-ON8525701A.0061F816-8525701A.0063F646@unimin.com>
View as plain text  
SGreen@stripped wrote:

> Michael's last answer:
> 
> 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;
> 
> Should do all of what you want except exclude the original user (so that 
> the user cannot become their own buddy). To do that I would change it to 
> read
> 
> 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
>         and u.UserID != '$userid';
> 
> Can you show us some sample data and the result of either of these queries 
> and explain what's wrong? I agree with Michael that this should work for 
> what you need.
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

I was just writing to say the same thing.  I would only add that since 
userId is numeric, we should all drop the quotes around $userid.

   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
     AND u.UserID != $userid;

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