MySQL 数据库 JOIN 表和搜索 WHERE <多列>

Question

我有 3 个表tweet、room、room_tweet:

       tweet                    room                          room_tweet
+----+------+-----+     +----+------+-----+     +----+---------+----------+----------+
| id | text | ... |     | id | name | ... |     | id | room_id | tweet_id | disabled |
+----+------+-----+     +----+------+-----+     +----+---------+----------+----------+
| 10 | a... | ... |     | 20 | a... | ... |     | 80 |   20    |    10    |     1    |
| 11 | b... | ... |     | 21 | b... | ... |     | 81 |   20    |    11    |     0    |
| 12 | c... | ... |     | 22 | c... | ... |     | 82 |   21    |    10    |     1    |
| 13 | d... | ... |     +----+------+-----+     | 83 |   20    |    12    |     1    |
+----+------+-----+                             | 84 |   22    |    10    |     0    |
                                                | 85 |   21    |    11    |     1    |
                                                +----+---------+----------+----------+

我需要一个查询来获取所有推文并显示特定房间中的禁用状态。

所有推文都显示在所有房间中，每条推文都有已禁用状态，并且每个房间都不同房间(同一个推文在每个房间中具有不同的禁用状态)这是通过room_tweet实现的表。

这是我到目前为止编写的查询:

SELECT t.id AS tweet_id, t.text AS tweet, rt.disabled, r.id AS room_id, r.name AS room  
FROM tweet as t 
LEFT JOIN room_tweet as rt ON t.id = rt.tweet_id 
LEFT JOIN room as r ON rt.room_id = r.id
WHERE r.id = 21 OR r.id IS NULL

并非所有推文都位于room_tweet表中以将其与房间相关联。因此，许多推文将在结果集中中具有对房间的NULL引用，同样的情况也适用于房间，某些房间将没有与其关联的推文。 [这并不意味着推文不应出现在结果集上。]

所有推文都应在结果集中中出现一次(仅一次)，并且禁用状态为[1、0或NULL] .

如果所有推文碰巧在room_tweet表上有关联，这将是一个简单的查询，但事实并非如此。由于推文是全天创建的，并且房间是随机创建的，因此room_tweet表不会也不应该具有所有关联。

Result without WHERE:
+----------+-------+----------+---------+------+
| tweet_id | tweet | disabled | room_id | room |
+----------+-------+----------+---------+------+
|    10    | a...  |    1     |    20   | a... |
|    10    | a...  |    1     |    21   | b... |
|    10    | a...  |    0     |    22   | c... |
|    11    | b...  |    0     |    20   | a... |
|    11    | b...  |    1     |    21   | b... |
|    12    | c...  |    1     |    20   | a... |
|    13    | d...  |   NULL   |   NULL  | NULL |
+----------+-------+----------+---------+------+
Tweets are duplicated multiple times ex: tweet 10 shows 3 times.

Result with (WHERE r.id = 21 OR r.id IS NULL)
+----------+-------+----------+---------+------+
| tweet_id | tweet | disabled | room_id | room |
+----------+-------+----------+---------+------+
|    10    | a...  |    1     |    21   | b... |
|    11    | b...  |    1     |    21   | b... |
|    13    | d...  |   NULL   |   NULL  | NULL |
+----------+-------+----------+---------+------+    
Tweets are missing!, only tweet with id 10, 11 and 13 show up. This is because 
the other tweets have been associated with other rooms and therefore are not 
**NULL** anymore. (All tweets should appear once).

正如您在结果集上看到的，并非所有推文都会显示(当使用WHERE时)以及其他问题是与 room_tweet 表上的多个房间关联的推文，因此它们在结果集上重复强>。 (我只想查看每条推文一次!)

所以问题是:我在查询中缺少什么，是否有可能获得我期望的结果？

是否可以修复此行为？如果是这样，是我的查询有问题还是表格错误？

如果你想测试查询，这里是在 phpMyAdmin 中创建所有表和数据的 SQL:

CREATE TABLE `room` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `...` varchar(3) NOT NULL DEFAULT '...', PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
CREATE TABLE `tweet` (`id` int(11) NOT NULL AUTO_INCREMENT, `text` varchar(140) NOT NULL, `...` varchar(3) NOT NULL DEFAULT '...', PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
CREATE TABLE `room_tweet` (`id` int(11) NOT NULL AUTO_INCREMENT, `room_id` int(11) NOT NULL, `tweet_id` int(11) NOT NULL, `disabled` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `fk_room_tweet_1` (`room_id`), KEY `fk_room_tweet_2` (`tweet_id`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=86 ;
ALTER TABLE `room_tweet` ADD CONSTRAINT `fk_room_tweet_1` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`), ADD CONSTRAINT `fk_room_tweet_2` FOREIGN KEY (`tweet_id`) REFERENCES `tweet` (`id`);    
INSERT INTO `room` (`id`, `name`, `...`) VALUES (20, 'a...', '...'), (21, 'b...', '...'), (22, 'c...', '...');    
INSERT INTO `tweet` (`id`, `text`, `...`) VALUES (10, 'a...', '...'), (11, 'b...', '...'), (12, 'c...', '...'), (13, 'd...', '...');
INSERT INTO `room_tweet` (`id`, `room_id`, `tweet_id`, `disabled`) VALUES (80, 20, 10, 1), (81, 20, 11, 0), (82, 21, 10, 1), (83, 20, 12, 1), (84, 22, 10, 0), (85, 21, 11, 1);

我尝试过 JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN，中午似乎解决了我的问题。

感谢您的宝贵时间，我再次需要的是一个结果集，其中包含显示已禁用的所有推文说明该特定房间。如果它没有禁用状态，则应显示NULL。

到目前为止最好的结果是这样的:

SELECT t.id AS tweet_id, t.text AS tweet, rt.disabled, r.id AS room_id, r.name AS room  
FROM tweet as t 
LEFT JOIN room_tweet as rt ON t.id = rt.tweet_id 
LEFT JOIN room as r ON rt.room_id = r.id

再次感谢您的宝贵时间。

Answer 1

您需要将 r.id 条件移至 on 子句:

SELECT t.id AS tweet_id, t.text AS tweet, rt.disabled, r.id AS room_id, r.name AS room  
FROM tweet as t 
LEFT JOIN room_tweet as rt ON t.id = rt.tweet_id 
LEFT JOIN room as r ON rt.room_id = r.id and r.id = 21;

通过此查询，只有当元素位于 21 号房间时才会匹配。