By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE yth_sections
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(100)
);
CREATE TABLE yth_rows
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(100),
section_id INT REFERENCES yth_sections (id)
);
CREATE TABLE yth_seats
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(100),
row_id INT REFERENCES yth_rows (id),
STATUS INT -- 1 = available, 0 = taken
);
INSERT INTO yth_sections (NAME) VALUES ('A'), ('B'), ('C'), ('D')
INSERT INTO yth_rows (NAME, section_id) VALUES
('1', 1),
('2', 1),
('3', 1),
('4', 1),
('1', 2),
('2', 2),
('3', 2),
('4', 2),
('1', 3),
('2', 3),
('3', 3),
('4', 3),
('1', 4),
('2', 4),
('3', 4),
('4', 4);
INSERT INTO yth_seats (NAME, row_id, STATUS)
SELECT CONCAT(rowName, '-', ROW_NUMBER() OVER (PARTITION BY r.id ORDER BY (SELECT 1))), r.id, 0 FROM information_schema.CHARACTER_SETS s1
CROSS JOIN (SELECT r.id, concat(s.NAME, '-', r.NAME) AS rowName FROM yth_rows r JOIN yth_sections s ON s.id = r.section_id) r
-- free up some seats: 2 in section B, 4 in C, 5 in D
update yth_seats
SET STATUS = 1
WHERE name IN ('B-2-40', 'B-3-15', 'C-1-4', 'C-4-6', 'C-3-9', 'C-3-14', 'D-1-1', 'D-1-2', 'D-1-3', 'D-1-4', 'D-1-5')
SELECT * FROM yth_seats WHERE status = 1
| id | NAME | row_id | STATUS |
|---|---|---|---|
| 240 | B-2-40 | 6 | 1 |
| 255 | B-3-15 | 7 | 1 |
| 324 | C-1-4 | 9 | 1 |
| 409 | C-3-9 | 11 | 1 |
| 414 | C-3-14 | 11 | 1 |
| 446 | C-4-6 | 12 | 1 |
| 481 | D-1-1 | 13 | 1 |
| 482 | D-1-2 | 13 | 1 |
| 483 | D-1-3 | 13 | 1 |
| 484 | D-1-4 | 13 | 1 |
| 485 | D-1-5 | 13 | 1 |
-- find me 4 seats!
SELECT s.*
FROM (SELECT se.id, COUNT(*) AS seats_available
FROM yth_seats s
JOIN yth_rows r
ON s.row_id = r.id
JOIN yth_sections se
ON se.id = r.section_id
WHERE STATUS = 1
GROUP BY se.id) section_available
JOIN yth_sections se
ON se.id = section_available.id
JOIN yth_rows r
ON r.section_id = se.id
JOIN yth_seats s
ON s.row_id = r.id
WHERE section_available.seats_available >= 4
AND s.status = 1
-- your prios, I'm just using
ORDER BY se.NAME
LIMIT 4
| id | NAME | row_id | STATUS |
|---|---|---|---|
| 414 | C-3-14 | 11 | 1 |
| 409 | C-3-9 | 11 | 1 |
| 324 | C-1-4 | 9 | 1 |
| 446 | C-4-6 | 12 | 1 |