
<h1>
SQL for find next available Episode</h1>
<h2>
Problem</h2>
<p>
<a href="https://repo.anhonesthost.net/HPR/hpr_hub/issues/71" rel="noopener noreferrer" target="_blank">
https://repo.anhonesthost.net/HPR/hpr_hub/issues/71</a>
</p>
<p>
<br>
</p>
<blockquote>
We need to get the next_free_slot, and this needs to take into account the Eps and reservations table.</blockquote>
<ul>
<li>
Eps table contain recorded and uploaded shows.</li>
<li>
reservations table reserve episodes that have not been recorded.</li>
<li>
There are existing queries to find the next free slot, but it does not include reservations.</li>
<li>
<strong>
HPR SQL dump</strong>
 - <a href="https://hackerpublicradio.org/hpr.sql" rel="noopener noreferrer" target="_blank">
https://hackerpublicradio.org/hpr.sql</a>
</li>
</ul>
<h2>
<br>
</h2>
<h2>
TLDR</h2>
<ul>
<li>
Create a list of all episode IDs from eps and reservations tables using SQL <code>
UNION</code>
</li>
<li>
Join the union list + 1 with the IDs from the eps and reservation tables</li>
<li>
WHERE clause to select rows in the union list +1 that are not in eps and not in reservations</li>
<li>
Order by and Limit to select the smallest</li>
</ul>
<h2>
<br>
</h2>
<h2>
Test Data</h2>
<ul>
<li>
Test data to make developing query easier.</li>
<li>
Simpler numbers so it is easier to spot patterns</li>
<li>
Same table and column names, and store them in a different database.</li>
</ul>
<h3>
Create the test data tables</h3>
<pre class="ql-syntax" spellcheck="false">
-- 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)
);
</pre>
<h3>
Insert the test data</h3>
<pre class="ql-syntax" spellcheck="false">
-- 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);
</pre>
<h3>
Print the test data tables</h3>
<pre class="ql-syntax" spellcheck="false">
-- 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 |
+------+
</pre>
<h2>
<br>
</h2>
<h2>
Join Types</h2>
<ul>
<li>
UNION - combine results of 2 queries</li>
<li>
INNER - Only records that are in both tables</li>
<li>
LEFT - All the Results in the Left column and matching results in the Right</li>
</ul>
<h3>
Test data Join Examples</h3>
<ul>
<li>
In the test data, the ID <code>
1004</code>
 is in both the episodes and reservations table.</li>
<li>
This will not occur in the real HPR database, but is useful to how different join types work</li>
<li>
Example queries with <code>
INNER</code>
, <code>
RIGHT</code>
, and <code>
LEFT</code>
 joins.</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
MariaDB [next_av]&gt; 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]&gt; 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]&gt; 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)
</pre>
<h2>
<br>
</h2>
<h2>
Combine episode and reserved IDs</h2>
<ul>
<li>
Create a single list of IDs from both tables with <code>
UNION</code>
</li>
<li>
UNION combines the results of 2 queries</li>
<li>
SQL <code>
as</code>
 keyword renames query results</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
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 |
+---------+
</pre>
<h2>
<br>
</h2>
<h2>
Join tables with the Union</h2>
<ul>
<li>
Left Joins Keep everything in the Left column</li>
<li>
Use the Union of all IDs and join with Eps and reservations</li>
<li>
The SQL will print a table of all the ids</li>
<li>
the eps and reservation columns will have the id if they match or NULL if there is not a match.</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
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 |
+---------+---------+--------------+
</pre>
<h2>
<br>
</h2>
<h2>
Join with union plus 1 -- All Results</h2>
<ul>
<li>
Add an additional column of the union ids +1</li>
<li>
Join the Union plus one list with the episodes and reservations</li>
<li>
Available episodes will have NULL in the eps and reservations column</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
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 |
+---------+--------------+---------+--------------+
</pre>
<h2>
<br>
</h2>
<h2>
Add a WHERE clause</h2>
<ul>
<li>
Add a where clause to only print rows were eps and reservations are null</li>
<li>
The smallest number in the +1 column will be the next available</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
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)
</pre>
<h2>
<br>
</h2>
<h2>
Add a limit and only select the id</h2>
<ul>
<li>
Sort and select the 1st row</li>
</ul>
<pre class="ql-syntax" spellcheck="false">
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 |
+--------------+
</pre>
<p>
<br>
</p>


