Angelo Zanetti <[EMAIL PROTECTED]> 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
