Thanks guys it worked!!! I really appreciate your help.
this is the one that worked:
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 Stassen wrote:
> [EMAIL PROTECTED] 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
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
