It works if I do AND instead of WHERE
Go figure

LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
AND friends.userA = 79760

--
Dave

Jerry Schwartz wrote:
> I think your problem is that you can't have a "missing" friends record that
> also has a non-null value for friends.userA. If friends.userA = 79760, then
> you've found a record.
>
> You can have records where userA is something valid and UserB is null, but
> then you can't join on UserB.
>
> Does that ?
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>
>
>> -----Original Message-----
>> From: Critters [mailto:critters@desktopcreatures.com]
>> Sent: Wednesday, August 22, 2007 12:23 PM
>> To: MySQL General
>> Subject: Left join is not doing what I thought it should do.
>>
>> I have 3 tables
>> A users table (userID, userName)
>> A leaderboard table (userID, score)
>> A friends table (userIDA, userIDB)
>>
>> I would like to produce the following result:
>>
>> userName, score, userIDA
>> Dave, 100, 1
>> Simon, 200, 5
>> Paul, 300, NULL
>>
>> The 3rd record is NULL as there is no record in "friends"
>> with a userIDB
>> matching users (or leaderboard) userID
>>
>> I have tried this:
>>
>> SELECT users.username, gameLeaderboards.playerpoints, friends.userA
>> FROM gameLeaderboards
>> JOIN users ON gameLeaderboards.userID = users.ID
>> LEFT JOIN friends ON gameLeaderboards.userID = friends.userB
>> WHERE friends.userA = 79760
>>
>> The where is so there is only a value in the "userIDA" column if the
>> user is friends with userID 79760
>>
>> But what I get instead of lots of records with 79760 and
>> NULLs is just
>> records from the leaderboard table that have a matching userID in the
>> friend table, hmmf
>>
>> Any ideas?
>>
>> Thanks
>> -
>> Dave
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>>
>>
>>
>
>
>
>
>