List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 11 2003 9:46am
Subject:RE: Left Join - Revisted
View as plain text  
I am not sure if I fully understand your problem, but I think you have
to move the IS NULL to the LEFT JOIN condition.

Cheers
/rudy

SELECT distinct 
       useronline.uname, 
       penpals_fav.fav_user_id, 
       penpals_fav.ID, 
       penpals_privmsgs_block.blocked_id
  FROM useronline, 
       penpals_fav 
  LEFT JOIN penpals_privmsgs_block ON
                        penpals_fav.user_id =
penpals_privmsgs_block.user_id
                    AND penpals_privmsgs_block.blocked_id IS NULL
 WHERE penpals_fav.user_id = $useridsession 
   AND penpals_fav.fav_user_name = useronline.uname 




-----Original Message-----
From: vernon [mailto:vernon@stripped] 
Sent: donderdag 10 juli 2003 17:16
To: mysql@stripped
Subject: Left Join - Revisted

Previously I've asked questions in reagrds to this matter and thought I
had 
resolved this issue only to discover that it is not working as thought.

I am trying to create a Friend's Online list that displays the number of

friends in a members friend's list, with the exception of when the other

person blocks that user as being a friend.

Here I select the DISTINCT user name from the user's online table 
(useronline.uname) and select the other fields needed:

SELECT distinct useronline.uname, penpals_fav.fav_user_id,
penpals_fav.ID, 
penpals_privmsgs_block.blocked_id

Next I select the tables needed and create the left join from the
blocked 
users table (penpals_privmsgs_block) where the user id matches:

FROM useronline, penpals_fav left join penpals_privmsgs_block ON 
penpals_fav.user_id = penpals_privmsgs_block.user_id

Then I match up the user in friend's online table (penpals_fav.user_id =

$useridsession) to get friends online (penpals_fav.fav_user_name = 
useronline.uname) but only if the value in the blocked id field is null
(penpals_privmsgs_block.blocked_id IS NULL).

WHERE penpals_fav.user_id = $useridsession AND penpals_fav.fav_user_name
= 
useronline.uname AND penpals_privmsgs_block.blocked_id IS NULL

The problem with all this is if the user has one blocked user it takes
that 
value it joins it in every returned record. Here is a saple of the
returned 
results:

User Name    UserID  ID   BlockedUserID
onlineuser1  2557    105  3807
onlineuser2  3400    170  3807
onlineuser3  1502    188  3807
onlineuser4  3807    199  3807

Notice that the blocked user id is 3807, but that is only one record and
now 
it is added to the end of each record and therefore no records are
actually 
returned beacuse of the IS NULL command.

What am I do wrong?

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Access deniedAsterix11 Mar
  • Re: Access deniedChristian Mack11 Mar
  • Re: Can mysql handle this load?nospam9 Jul
  • RE: Can mysql handle this load?Andy Eastham9 Jul
  • Re: Can mysql handle this load?Krasimir_Slaveykov10 Jul
  • Left Join - Revistedvernon10 Jul
RE: Can mysql handle this load?Mike Hillyer9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: order of table joins or where clauses relevant?Rudy Metzger10 Jul
RE: Left Join - RevistedRudy Metzger11 Jul
RE: Can mysql handle this load?Rudy Metzger14 Jul