php - Mysql:当加入字段等于条件时选择

Question

图像表

+----------+---------------+---------------+--------------+
| image_id | image_user_id | profile_image | image_status |
+----------+---------------+---------------+--------------+
|        1 |             1 |    834098.png |         live |
|        2 |             2 |    347903.jpg |      pending |
|        3 |             3 |    447903.jpg |      pending |
+----------+---------------+---------------+--------------+

评论表

+------------+-----------------+---------------+
| comment_id | comment_user_id | text          |
+------------+---------------------------------+
|          1 |               1 | great article |
|          2 |               2 |       not bad |
|          3 |               3 |         lorem |
+------------+-----------------+---------------+

SQL 查询

SELECT
    profile_image,
    comment_id
FROM comment
LEFT JOIN image ON image_user_id = comment_user_id
WHERE image_status = 'live'
LIMIT 7

以上代码仅在相关的image_pending 字段设置为live 时读取评论。当 image_status 为 live 时，如何更改代码以使其读取 profile_image？

上面的代码会输出:

array( 'profile_image' => '834098.png', 'comment_id' => 1 )

它应该输出:

array(
    array( 'profile_image' => '834098.png', 'comment_id' => 1 )
    array( 'comment_id' => 2 )
    array( 'comment_id' => 3 )
)

Answer 1

你想要这样的东西吗？

SELECT
    profile_image,
    comment_id
FROM comment
LEFT JOIN image ON image_user_id = comment_user_id AND image_status = 'live'
LIMIT 7

将返回:

PROFILE_IMAGE   COMMENT_ID
834098.png          1
(null)              2
(null)              3

sqlfiddle demo

当您想过滤连接条件时，您在 where 子句中使用 image_status = 'live' 过滤结果。