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? 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:[EMAIL PROTECTED] > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
