SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:59 AM
| ||||
| ||||
 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      |
|
02-28-2008, 06:59 AM
| |||
| |||
| RE: Left join is not doing what I thought it should do. 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: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 > > |
|
02-28-2008, 06:59 AM
| |||
| |||
| 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: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 >> >> >> > > > > > |
|
02-28-2008, 06:59 AM
| ||||
| ||||
| RE: Left join is not doing what I thought it should do. 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:critters@desktopcreatures.com] > 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: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 > >> > >> > >> > > > > > > > > > > > > |
Linear Mode
