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

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 ?

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
>
>

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 ?
>
> 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
>>
>>
>>
>
>
>
>
>

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 ?
> >
> > 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
> >>
> >>
> >>
> >
> >
> >
> >
> >
>
>