MySQL LEFT JOIN Problem - Missing LEFT column

Hi,

I'm having problems with an SQL query used to display custom profile fields and any (optional) corresponding values.

Here is the SQL query I'm using:

SELECT pf.`id`, pf.`name`, pv.`value` FROM `profile_fields` AS pf
LEFT JOIN `profile_values` AS pv ON (pf.`id` = pv.`field_id`)
WHERE (pf.`site_id` = '0' OR pf.`site_id` = '%d') AND (pv.`user_id` = '%d' OR pv.`user_id` IS NULL)
ORDER BY pf.`order` ASC

The problem I'm having is that any columns with no corresponding profile_values records are not shown at all, when they should show, but just with an empty value.

Many thanks!

From stackoverflow

• Try moving the profile values conditions to the JOIN statement:

   SELECT pf.`id`, pf.`name`, pv.`value` FROM `profile_fields` AS pf
   LEFT JOIN `profile_values` AS pv ON (
           pf.`id` = pv.`field_id`  AND 
           (pv.`user_id` = '%d' OR pv.`user_id` IS NULL)
    )
   WHERE (pf.`site_id` = '0' OR pf.`site_id` = '%d')
   ORDER BY pf.`order` ASC
  

  Nick : Many thanks - that did the trick! Never knew I was able to add that sort of thing in an "ON" clause!

  Ruben : Shouldn't the ... OR ... pv.user_id IS NULL) part of the original query take care of showing the profile_fields without all optional profile_values available?

  Nick : That's what I thought, but it didn't for some reason. It works with Eran's code however, so I probably put it in the wrong place.

  Bill Karwin : You can probably get rid of the 'OR pv.user_id IS NULL' after you move the condition into the join condition.

  bbtang : what is that "%d"???