Associate records non existing from two tables-CodePudding

I have a couple of tables on mysql: room and amenities, a room can have multiple amenities, the table amenity is not normalize and will duplicate the records and the only thing will change will be the id of the room associated, I need that for each room that I have, have the same amount of entries as unique amenities I have in the amenity table, for example if I have 2 rooms and I have 4 distinct amenities I need the result be 2*4 even if I don't have an amenity association, I know I can do the left outer join but for some reason the result is not the expected

I cannot update the data base design and I need to work on what I have, other wise I will break multiple services that works as is now, the table's definition is as follows:

-- 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

Could somebody knows how can I achieve the expected behavior

CodePudding user response：

Cross join with a subquery that gets all the available amenities

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);

DEMO

This assumes that every amenity is available in at least one room, so that the subquery will find them all. This is the reason why normalization is important -- something that's unused shouldn't just disappear.