HPR4378: SQL to get the next_free_slot

Hacker Public Radio

SQL for find next available Episode Problem https://repo.anhonesthost.net/HPR/hpr_hub/issues/71 We need to get the next_free_slot, and this needs to take into account the Eps and reservations table. Eps table contain recorded and uploaded shows. reservations table reserve episodes that have not been recorded. There are existing queries to find the next free slot, but it does not include reservations. HPR SQL dump - https://hackerpublicradio.org/hpr.sql TLDR Create a list of all episode IDs from eps and reservations tables using SQL UNION Join the union list + 1 with the IDs from the eps and reservation tables WHERE clause to select rows in the union list +1 that are not in eps and not in reservations Order by and Limit to select the smallest Test Data Test data to make developing query easier. Simpler numbers so it is easier to spot patterns Same table and column names, and store them in a different database. Create the test data tables -- Create eps CREATE TABLE IF NOT EXISTS eps ( id INT, PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS reservations ( ep_num INT, PRIMARY KEY (ep_num) ); Insert the test data -- Inserts INSERT INTO eps (id) VALUES (1001); INSERT INTO eps (id) VALUES (1002); INSERT INTO eps (id) VALUES (1003); INSERT INTO eps (id) VALUES (1004); INSERT INTO eps (id) VALUES (1011); INSERT INTO eps (id) VALUES (1021); INSERT INTO eps (id) VALUES (1031); INSERT INTO eps (id) VALUES (1041); INSERT INTO reservations (ep_num) VALUES (1004); INSERT INTO reservations (ep_num) VALUES (1005); INSERT INTO reservations (ep_num) VALUES (1006); INSERT INTO reservations (ep_num) VALUES (1010); INSERT INTO reservations (ep_num) VALUES (1016); Print the test data tables -- Episodes SELECT e.id as e_id FROM eps e order by e.id; +------+ | e_id | +------+ | 1001 | | 1002 | | 1003 | | 1004 | | 1011 | | 1021 | | 1031 | | 1041 | +------+ SELECT r.ep_num as r_id FROM reservations r; +------+ | r_id | +------+ | 1004 | | 1005 | | 1006 | | 1010 | | 1016 | +------+ Join Types UNION - combine results of 2 queries INNER - Only records that are in both tables LEFT - All the Results in the Left column and matching results in the Right Test data Join Examples In the test data, the ID 1004 is in both the episodes and reservations table. This will not occur in the real HPR database, but is useful to how different join types work Example queries with INNER , RIGHT , and LEFT joins. MariaDB [next_av]> SELECT e.id ,r.ep_num FROM eps e INNER JOIN reservations r ON e.id = r.ep_num; +------+--------+ | id | ep_num | +------+--------+ | 1004 | 1004 | +------+--------+ 1 row in set (0.001 sec) MariaDB [next_av]> SELECT e.id ,r.ep_num FROM eps e RIGHT JOIN reservations r ON e.id = r.ep_num; +------+--------+ | id | ep_num | +------+--------+ | 1004 | 1004 | | NULL | 1005 | | NULL | 1006 | | NULL | 1010 | | NULL | 1016 | +------+--------+ 5 rows in set (0.001 sec) MariaDB [next_av]> SELECT e.id ,r.ep_num FROM eps e LEFT JOIN reservations r ON e.id = r.ep_num; +---