need SQL help in finding pairs of values that don't exist in a table

Question

The table my_table has a bunch of columns, but the only two I really care about are [timestamp (5 min increments), id]

for example

timestamp	id
2022-03-02 03:00:00	1
2022-03-02 03:05:00	1
2022-03-02 03:00:00	2
2022-03-02 03:05:00	2
2022-03-02 03:00:00	3
2022-03-02 03:00:00	4
2022-03-02 03:05:00	4
2022-03-02 03:00:00	5
2022-03-02 03:05:00	5

Now, as you may notice, the pair | 2022-03-02 03:05:00 | 3 | is missing. This is the scenario I want to return the missing pair for. For every timestamp in a given range of values I'm interested in, I want to find, if it has a corresponding id. the id's are all in the list (1,2,3,4,5).

I tried the following:

select * 
       from 
       (select distinct timestamp from my_table) 
       as a  
       left join (select timestamp, id from my_table) 
       as b 
       on a.timestamp=b.timestamp 
where id is null;

I thought that this would find what i was looking for, but alas the pair values I am looking for don't exist in the given table so they don't show up in the join. My other idea is to create a temp table with all the timestamp values in a certain range pair with each id, so 5 pairs for every timestamp, and then outer join that with my original table to find null values, but I'm not sure how to create such a temp table.

Any ideas?

Answer 1

You need to create tally tables. There are plenty of posts in the googleshpere depending on your flavour of SQL. Create one for all the time periods you are interested in and one for your IDs. CROSS JOIN to create a table of all the possible values and then LEFT JOIN your source table and apply a WHERE clause to filter out any records where your source table has been found. The resulting data set is every 'missing' record.