List:General Discussion« Previous MessageNext Message »
From:Angelo Zanetti Date:June 8 2005 5:38pm
Subject:Re: LEFT JOIN?
View as plain text  
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
>
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