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 |
| 1031 |   NULL |
| 1041 |   NULL |
+------+--------+
8 rows in set (0.001 sec)

Combine episode and reserved IDs

• Create a single list of IDs from both tables with UNION
• UNION combines the results of 2 queries
• SQL as keyword renames query results

SELECT id as all_ids
FROM eps
UNION
select ep_num FROM  reservations
;
+---------+
| all_ids |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1004 |
|    1011 |
|    1021 |
|    1031 |
|    1041 |
|    1005 |
|    1006 |
|    1010 |
|    1016 |
+---------+

Join tables with the Union

• Left Joins Keep everything in the Left column
• Use the Union of all IDs and join with Eps and reservations
• The SQL will print a table of all the ids
• the eps and reservation columns will have the id if they match or NULL if there is not a match.

select all_ids.id as all_ids ,eps.id as eps_ids , r.ep_num as reserved_ids FROM
    (SELECT id
    FROM eps
    UNION
    select ep_num FROM  reservations)
    as all_ids
LEFT JOIN eps
ON all_ids.id = eps.id
LEFT JOIN reservations r
ON all_ids.id = r.ep_num
;
+---------+---------+--------------+
| all_ids | eps_ids | reserved_ids |
+---------+---------+--------------+
|    1001 |    1001 |         NULL |
|    1002 |    1002 |         NULL |
|    1003 |    1003 |         NULL |
|    1004 |    1004 |         1004 |
|    1011 |    1011 |         NULL |
|    1021 |    1021 |         NULL |
|    1031 |    1031 |         NULL |
|    1041 |    1041 |         NULL |
|    1005 |    NULL |         1005 |
|    1006 |    NULL |         1006 |
|    1010 |    NULL |         1010 |
|    1016 |    NULL |         1016 |
+---------+---------+--------------+

Join with union plus 1 -- All Results

• Add an additional column of the union ids +1
• Join the Union plus one list with the episodes and reservations
• Available episodes will have NULL in the eps and reservations column

select all_ids.id as all_ids,all_ids.id+1 as all_ids_plus ,eps.id as eps_ids , r.ep_num as reserved_ids FROM
    (SELECT id
    FROM eps
    UNION
    select ep_num FROM  reservations)
    as all_ids
LEFT JOIN eps
ON all_ids.id+1 = eps.id
LEFT JOIN reservations r
ON all_ids.id +1 = r.ep_num
ORDER BY all_ids
;
+---------+--------------+---------+--------------+
| all_ids | all_ids_plus | eps_ids | reserved_ids |
+---------+--------------+---------+--------------+
|    1001 |         1002 |    1002 |         NULL |
|    1002 |         1003 |    1003 |         NULL |
|    1003 |         1004 |    1004 |         1004 |
|    1004 |         1005 |    NULL |         1005 |
|    1005 |         1006 |    NULL |         1006 |
|    1006 |         1007 |    NULL |         NULL |
|    1010 |         1011 |    1011 |         NULL |
|    1011 |         1012 |    NULL |         NULL |
|    1016 |         1017 |    NULL |         NULL |
|    1021 |         1022 |    NULL |         NULL |
|    1031 |         1032 |    NULL |         NULL |
|    1041 |         1042 |    NULL |         NULL |
+---------+--------------+---------+--------------+

Add a WHERE clause

• Add a where clause to only print rows were eps and reservations are null
• The smallest number in the +1 column will be the next available

select all_ids.id as all_ids,all_ids.id+1 as all_ids_plus ,eps.id as eps_ids , r.ep_num as reserved_ids FROM
    (SELECT id
    FROM eps
    UNION
    select ep_num FROM  reservations)
    as all_ids
LEFT JOIN eps
ON all_ids.id+1 = eps.id
LEFT JOIN reservations r
ON all_ids.id +1 = r.ep_num
WHERE eps.id is Null and r.ep_num is NULL
ORDER BY all_ids
;
+---------+--------------+---------+--------------+
| all_ids | all_ids_plus | eps_ids | reserved_ids |
+---------+--------------+---------+--------------+
|    1006 |         1007 |    NULL |         NULL |
|    1011 |         1012 |    NULL |         NULL |
|    1016 |         1017 |    NULL |         NULL |
|    1021 |         1022 |    NULL |         NULL |
|    1031 |         1032 |    NULL |         NULL |
|    1041 |         1042 |    NULL |         NULL |
+---------+--------------+---------+--------------+
6 rows in set (0.002 sec)

Add a limit and only select the id

• Sort and select the 1st row

select all_ids.id+1 as available_id FROM
    (SELECT id
    FROM eps
    UNION
    select ep_num FROM  reservations)
    as all_ids
LEFT JOIN eps
ON all_ids.id+1 = eps.id
LEFT JOIN reservations r
ON all_ids.id +1 = r.ep_num
WHERE eps.id is Null and r.ep_num is NULL
ORDER BY available_id
LIMIT 1
;
+--------------+
| available_id |
+--------------+
|         1007 |
+--------------+