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]
