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 |

+--------------+