Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Jerry Schwartz (jschwartzthe-infoshop.com)
Date: Wed Aug 22 2007 - 13:30:54 CDT
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 help?
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: Critters [mailto:crittersdesktopcreatures.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?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql