Php、MySql 连接两个表并显示有关系和没有关系的所有记录

Question

我一直在绞尽脑汁地想出这个查询:

我有两个表:

Labels

id      name
====================
1       Inbox
2       Sent
3       Trash


Messages

id      label_id    user_id     name                    status                  
===============================================================
1       1           1           My First Message        1
2       1           1           My Second Message       1
3       2           1           My Third Message        1
4       0           1           My Fourth Message       1
5       0           1           My Fifth Message        1

这是我的查询:

SELECT 
  m.name,
  m.id,
  m.label_id,
  l.name AS labelname
FROM
  `Messages` m 
  LEFT JOIN `Labels` l 
    ON l.id = m.label_id 
WHERE m.user_id = 1 
  AND m.status = 1;

期望的结果是显示所有标签，无论消息 label_id 是否匹配

类似于:

Inbox
=================
My First Message
My Second Message
==================

Sent
=================
My Third Message
=================

Trash
=================

=================
My Fourth Message
My Fifth Message
=================

正如您所看到的，最后两条消息没有任何 label_id，但我仍然想在最后显示它们以及当前没有分配任何内容的垃圾箱标签。

Answer 1

您的查询:

(
         SELECT  m.name, m.id, m.label_id, l.name AS labelname
           FROM  `Messages` m
      LEFT JOIN  `Labels` l   
             ON  l.id = m.label_id 
          WHERE  m.user_id = 1 
            AND  m.status = 1
)
UNION
(
         SELECT  m.name, m.id, m.label_id, l.name AS labelname
           FROM  `Labels` l
      LEFT JOIN  `Messages` m   
             ON  l.id = m.label_id AND m.user_id = 1 AND m.status =1
)

sqlFiddle demo

使用UNION将原始结果与倒置LEFT JOIN的结果合并。

请注意不同的ON条件:在第二个SELECT中使用WHERE会导致虚假结果。