mysql - 使用条件子句连接多个 MySQL 表

Question

出于教育目的，我需要加入三个表。

Table: users
 id | username | photo
  1 | me       | my_photo
  2 | my_friend| friend's photo
  3 | somebody | somebody`s photo

Table: chat
 mid | sender    | receiver | created
  1  | me        | my friend| 2016-12-27 09:32:2
  2  | my_friend | me       | Y-m-d h:i:s  
  3  | me        | somebody | 2016-12-27 09:32:2
  4  | somebody  | me       | Y-m-d h:i:s  

Table:favorites
  id | subscriber | subscribed_to
   1 | me         | my_friend

期望的输出是

Conversations:
username  | photo            | created            | subscribed
me        | my friend's photo| 2016-12-27 09:32:2 | yes
my_friend | my foto          | Y-m-d h:i:s        | no
me        | somebody's foto  | 2016-12-27 09:32:2 | no
somebody  | my foto          | Y-m-d h:i:s        | no

目前我使用该代码完成了第一部分:

$qmessage = mysql_query("
select c.*, u.photo
from chat c
join users u on u.username = c.sender
WHERE   (sender = '$login_session' AND receiver = '$uid') OR
        (sender = '$uid' AND receiver = '$login_session')
");

Answer 1

我认为您在这里真正需要的只是一系列联接。以下查询中唯一可能需要解释的部分是最后的左连接。我使用发送者和接收者列将 chat 表连接到 favorites 表。然后我使用 CASE 表达式来检查给定记录是否匹配。如果是，则假定存在订阅并打印 'yes'，否则打印 'no'。

SELECT t1.sender AS username,
       COALESCE(t2.photo, 'NA') AS photo
       t1.created,
       CASE WHEN t3.subscriber IS NOT NULL THEN 'yes' ELSE 'no' END AS subscribed
FROM chat t1
LEFT JOIN users t2
    ON t1.receiver = t2.username
LEFT JOIN favorites t3
    ON t1.sender = t3.subscriber AND
       t1.receiver = t3.subscribed_to