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:[EMAIL PROTECTED]
Sent: donderdag 10 juli 2003 17:16
To: [EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
