关联两个表中不存在的记录

Question

我在mysql上有几张桌子：房间和便利设施，一个房间可以有多个便利设施，桌子的便利设施没有标准化，会重复记录，唯一会改变的是相关房间的id，我需要我拥有的每个房间都有与便利设施表中唯一便利设施相同的条目，例如，如果我有2个房间，我有4个不同的便利设施，我需要结果是2*4，即使我没有便利设施协会，我知道我可以进行左外连接，但由于某种原因，结果不是预期的

我无法更新数据库设计，我需要处理我所拥有的，否则我将破坏目前工作的多个服务，表的定义如下：

-- room definition
CREATE TABLE `room` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
-- amenity definition
CREATE TABLE `amenity` (
`id` int NOT NULL AUTO_INCREMENT,
`room_id` int NOT NULL,
`amenity` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`checked` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `room_key_idx` (`room_id`),
CONSTRAINT `room_key` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
-- Then insert the following records for rooms
INSERT INTO room (name) VALUES('room1');
INSERT INTO room (name) VALUES('room2');
-- Then insert the following records for amenities
INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity1', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity1', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity2', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity2', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity3', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity4', 0);
-- As you can see I dont have association between Amenity3 and room 2 as well for amenity4 room1
-- if I just join them I got something like this
select * from room r join amenity a on r.id =a.room_id where a.room_id in(1,2);
1   room1   1   1   Amenity1    0
1   room1   2   1   Amenity2    0
1   room1   3   1   Amenity3    0
2   room2   4   2   Amenity1    0
2   room2   5   2   Amenity2    0
2   room2   6   2   Amenity4    0
-- I want for each distinct amenity to match the room even if there is no amenity record associated, something like this
roomid idamenity amenity        
1      1         Amenity1
2      2         Amenity1
1      3         Amenity2
2      4         Amenity2
1      5         Amenity3
2      null      Amenity3
1      null      Amenity4
2      6         Amenity4
-- I have tried with left joins like this
select * from room r left outer join amenity a on a.room_id =r.id where r.id in(1,2);
-- But only return me 6 record and not the 8 that I am expecting

有人知道我如何才能达到预期的行为吗

Answer 1

与一个子查询交叉连接，该子查询可获得所有可用的便利设施

select r.id AS roomid, a2.id AS idamenity, a1.amenity
from room r 
CROSS join (
SELECT DISTINCT(amenity)
FROM amenity
) AS a1
LEFT JOIN amenity AS a2 ON a1.amenity = a2.amenity AND r.id = a2.room_id
where r.id in(1,2);

演示

这假设每个设施至少在一个房间中可用，这样子查询就会找到所有设施。这就是为什么规范化很重要的原因——一些未使用的东西不应该消失。