mysql - 带有 join-sql 的 case 语句

Question

我有一个表用户，其中有很多消息和很多联系人。

消息和联系人表之间的关系是通过contactMessages 表建立的。

表消息在contactMessages 上有一个外键，即message_id

表联系人在contactMessages 上有一个外键，即contact_id

用户发送的每条消息都存储为带有来自发件人用户字段的消息，接收者将在contactMessages表中将引用存储为contact_id，这将用于查看联系人并知道它是哪个用户发送。

现在我想选择用户拥有、发送和接收的所有消息。

我想查看引用该消息的用户的 id 是否在 contactMessages 表中，以了解用户是否发送或接收了该消息

我想以这种方式使用它，但我不知道如何查询它，我想它可能是通过连接但我是 SQL 的新手，我正在努力解决这个问题。

SELECT *,
CASE
    WHEN db.contacts.user_id = 1 THEN 'Inbox'
    WHEN user_id = 1 THEN 'Send'
END as type
FROM db.messages

用户表：

+----+----------+--------------+
| id |   name   |    email     |
+----+----------+--------------+
|  1 | lluis    | [e-mail] |
|  2 | paco     | [e-mail]  |
|  3 | ella     | [e-mail]  |
+----+----------+--------------+

联系人表：

+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
|  2 |      3 |
|  3 |      1 |
+----+--------+

联系人消息表：

+------------+------------+
| contact_id | message_id |
+------------+------------+
|          3 |          1 |
|          3 |          2 |
|          3 |          3 |
|          1 |          4 |  contact_id references the reciever of the msg
|          2 |          5 |  which will be found in contacts table
+------------+------------+  table.contacts.id = 
                             table.contactsMessagecontact_id
                             Once that contact found contacts.userId = 1

消息表：

+----+--------+--------------+
| id | userId |   message    |
+----+--------+--------------+
|  1 |      1 | HI           |
|  2 |      1 | How are you? |
|  3 |      1 | Thanks       |
|  4 |      2 | Bye          |   userId makes reference to the sender of
|  5 |      2 | Hola         |   the message
+----+--------+--------------+   

输出（消息表）：

+----+--------+--------------+-------+
| id | userId |   message    | type  |
+----+--------+--------------+-------+
|  1 |      1 | HI           | Send  |
|  2 |      1 | How are you? | Send  |
|  3 |      1 | Thanks       | Send  |
|  4 |      2 | Bye          | Inbox |
+----+--------+--------------+-------+

这将获得 id = 1 的用户的所有消息

标签： mysqlsql

Answer 1

我可以猜到下一个查询：

select 
    messages.*,
    CASE
    WHEN contactsMessage.contact_id = 1 THEN 'Inbox'
    WHEN messages.userId = 1 THEN 'Send'
  END as type
from messages
join contactsMessage on contactsMessage.message_id = messages.id
where userId = 1 or contact_id = 1;

SQL小提琴在这里