Justtechjobs.com Find a programming school near you






Online Campus Both


php-db | 2004071

[PHP-DB] RE: Subject OUTER JOIN ( WAS Subject: Multiple SELECT) From: Neil Smith [MVP, Digital media] (php <email protected>)
Date: 07/12/04

This answer is quite involved, see below.

>Content-Transfer-Encoding: 7bit
>Content-Type: text/plain; charset="ISO-8859-1"
>MIME-Version: 1.0
>To: php-db <email protected>
>Date: Sun, 11 Jul 2004 19:31:21 -0700
>From: "Marcjon" <marcjon <email protected>>
>Message-Id: <1089599481.18091.200157611 <email protected>>
>
>
>I know this issue comes up alot, I've looked through the archives
>looking for a solution, but none of them are what I want. I have two
>tables with share a common coloumn, username. One is the user table.
>It's used to store the user's password, username, email and so on. The
>other table is for the user's profile (if they choose to have one). It
>contains msnm, aim, yahoo etc address, birthdate and other stuff like
>that. So far I have this query:
>"SELECT
> forumusers.email AS email,forumusers.username AS
> username,forumusers.level AS level,
> forumusers.lastlogon1 AS lastlogon1,forumusers.lastlogon2 AS
> lastlogon2,forumusers.settings1 AS settings1,forumusers.confirmationcode
> AS confirmationcode,
> forumuserprofiles.sex AS sex, forumuserprofiles.birthdate AS
> birthdate,
> forumuserprofiles.address_msnm AS address_msnm,
> forumuserprofiles.address_aim AS address_aim,
> forumuserprofiles.address_yahoo AS address_yahoo,
> forumuserprofiles.address_icq AS address_icq
> FROM
> ".$godlyness['database_database'].".forumusers,".$godlyness['database_database'].".forumuserprofiles
> ".$filterbu."
> ORDER BY username
> "

You need to use LEFT OUTER JOIN syntax which will match rows on a key, and
return NULL where there is no match for the same key in another table. BTW
How does your query below work ????? There's no 'WHERE' filter !

So - lets try a stripped down version of your 1st query :

SELECT forumusers.*, forumuserprofile.*
FROM forumusers, forumuserprofile
WHERE forumusers.username = '".$selected_username."'
AND forumusers.username = forumuserprofile.username

This query matches all forumusers with the specified $selected_username,
and all forumuserprofiles which *also* match that username. Now, you need
to modify this query to recover the NON-MATCHING (ie, empty) user profiles
as well, this is where LEFT OUTER JOIN comes in :

SELECT forumusers.*, forumuserprofile.*
FROM forumusers
LEFT OUTER JOIN forumuserprofile
ON forumusers.username = forumuserprofile.username
WHERE forumusers.username = '".$selected_username."'

So, here we're still selecting the forumuser with username
$selected_username, but we *also* do a left join and include any rows where
there is a match as well as there is not a match for forumusers.username =
forumuseprofile.username. If there is *no* match, all the columns from
forumuserprofile will be NULL, if there *is* a matching forumusers.username
= forumuserprofile.username, then the columns from forumuserprofile will
contain values.

HTH
Neil 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php