MySQL LEFT JOIN问题 - 缺少LEFT列

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

我遇到了用于显示自定义配置文件字段和任何(可选)相应值的SQL查询的问题。

Here is the SQL query I'm using:

这是我正在使用的SQL查询:

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.

我遇到的问题是,任何没有相应profile_values记录的列都不显示,只显示它们,但只显示空值。

Many thanks!

1 个解决方案

#1

Try moving the profile values conditions to the JOIN statement:

尝试将配置文件值条件移动到JOIN语句:

 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