By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Categories ( c_id INT, name VARCHAR(8));
INSERT INTO Categories VALUES
( 1 , 'Thrill'),
( 2 , 'Leisure'),
( 3 , 'Kiddie');
CREATE TABLE Rides ( r_id INT, c_id INT);
INSERT INTO Rides VALUES
( 1 , 1),
( 2 , 1),
( 3 , 2),
( 4 , 2),
( 5 , 3),
( 6 , 3);
CREATE TABLE guest_history (h_id INT, g_id INT );
INSERT INTO guest_history VALUES
( 1 , 1),
( 2 , 1),
( 3 , 2),
( 4 , 3);
CREATE TABLE history_items (h_id INT, r_id INT);
INSERT INTO history_items VALUES
( 1 , 5),
( 2 , 6),
( 3 , 1),
( 3 , 2),
( 4 , 5);
SELECT *
FROM Categories
NATURAL JOIN Rides
NATURAL JOIN guest_history
NATURAL JOIN history_items
| r_id | h_id | c_id | name | g_id |
|---|---|---|---|---|
| 5 | 1 | 3 | Kiddie | 1 |
| 6 | 2 | 3 | Kiddie | 1 |
| 2 | 3 | 1 | Thrill | 2 |
| 1 | 3 | 1 | Thrill | 2 |
| 5 | 4 | 3 | Kiddie | 3 |
SELECT g_id
FROM Categories
NATURAL JOIN Rides
NATURAL JOIN guest_history
NATURAL JOIN history_items
GROUP BY g_id
HAVING COUNT(DISTINCT r_id) IN (0,
(SELECT COUNT(DISTINCT r_id)
FROM Categories
NATURAL JOIN Rides
WHERE name = 'Kiddie')
)
| g_id |
|---|
| 1 |
| 2 |