Content provided by HPR Volunteer and Hacker Public Radio. All podcast content including episodes, graphics, and podcast descriptions are uploaded and provided directly by HPR Volunteer and Hacker Public Radio or their podcast platform partner. If you believe someone is using your copyrighted work without your permission, you can follow the process outlined here https://podcastplayer.com/legal.

Hacker Public Radio « »
HPR4378: SQL to get the next_free_slot

6d ago

MP3•Episode home

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 | | 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 | +--------------+

Provide feedback on this episode.

860 episodes

#Tech News #Community Radio #Tech Interviews #Linux #Open #Software Freedom #HPR Volunteer #Hacker Public Radio #Tech Tips #Tech #Podcasting Education #Hobbies

HPR4378: SQL to get the next_free_slot

Hacker Public Radio

143 subscribers

published 6d ago

MP3•Episode home

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 | | 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 | +--------------+

Provide feedback on this episode.

860 episodes

#Tech News #Community Radio #Tech Interviews #Linux #Open #Software Freedom #HPR Volunteer #Hacker Public Radio #Tech Tips #Tech #Podcasting Education #Hobbies

Kaikki jaksot

Welcome to Player FM!

Player FM is scanning the web for high-quality podcasts for you to enjoy right now. It's the best podcast app and works on Android, iPhone, and the web. Signup to sync subscriptions across devices.

Listen to 500+ topics

Similar to Hacker Public Radio

Podcasts Worth a Listen

Hacker Public Radio « » HPR4378: SQL to get the next_free_slot

SQL for find next available Episode

Problem

TLDR

Test Data

Create the test data tables

Insert the test data

Print the test data tables

Join Types

Test data Join Examples

Combine episode and reserved IDs

Join tables with the Union

Join with union plus 1 -- All Results

Add a WHERE clause

Add a limit and only select the id

HPR4378: SQL to get the next_free_slot

SQL for find next available Episode

Problem

TLDR

Test Data

Create the test data tables

Insert the test data

Print the test data tables

Join Types

Test data Join Examples

Combine episode and reserved IDs

Join tables with the Union

Join with union plus 1 -- All Results

Add a WHERE clause

Add a limit and only select the id

Podcasts Worth a Listen

Welcome to Player FM!

Similar to Hacker Public Radio

Hacker Public Radio « »
HPR4378: SQL to get the next_free_slot