MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 8 2005 6:08pm
Subject:Re: LEFT JOIN?
View as plain text  
Angelo Zanetti <angelo@stripped> wrote on 06/08/2005 01:38:42 PM:

> 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
> >


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
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