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