MySQL LEFT JOIN 问题 - 缺少 LEFT 列

发布于 2024-07-09 19:55:20 字数 447 浏览 10 评论 0原文

我在使用用于显示自定义配置文件字段和任何（可选）相应值的 SQL 查询时遇到问题。

以下是我正在使用的 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

我遇到的问题是，任何没有相应 profile_values 记录的列在应该显示时根本不会显示，而只是显示空值。

非常感谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

九局 2024-07-16 19:55:20

尝试将配置文件值条件移至 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

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

回复收藏 0 原文

~没有更多了~