HPR4378: SQL to get the next_free_slot

This show has been flagged as Clean by the host.

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; +------+--------+ | id | ep_num | +------+--------+ | 1001 | NULL | | 1002 | NULL | | 1003 | NULL | | 1004 | 1004 | | 1011 | NULL | | 1021 | NULL |