By using AND, you've moved the test for 79760 into the JOIN condition. I wouldn't have thought of that, either. This seems to be a peculiarity of a LEFT JOIN. Normally, if you compare a NULL value against anything, even another NULL, the result is NULL (neither true nor false). I don't understand what that would do to an AND operation, I would think the result would be false.
I hope someone else can explain that to us. 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 3:30 PM > To: Jerry Schwartz > Cc: 'MySQL General' > Subject: Re: Left join is not doing what I thought it should do. > > 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 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]
