This answer is quite involved, see below.

Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="ISO-8859-1"
MIME-Version: 1.0
Date: Sun, 11 Jul 2004 19:31:21 -0700
From: "Marcjon" <[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 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, 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.


PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to