Use filter criteria from one table to dynamically select data from another table an union all results

user_235671 0
Viewed 115

Is it possible with sqlite to create such a query that loops over one table selecting one row at a time that I can use as parameters for another (complex) query and its subqueries? Here's a short demo of what I mean.

There is a table with the criteria:

create temp table filter as
    select 'foo' name, '+1 day' offset union
    select 'bar' name, '+3 day' offset union
    select 'baz' name, '+6 day' offset;

This is the table with the data:

create temp table data as
    select 'foo' name, '2021-12-05' day union
    select 'bar' name, '2021-12-06' day union
    select 'foo' name, '2021-12-07' day union
    select 'baz' name, '2021-12-08' day union
    select 'baz' name, '2021-12-09' day union
    select 'bar' name, '2021-12-10' day;

Now, I'd like to loop over the filter rows and use each row to query data from the second table then union all results. Obviously the below pseudo-code doesn't work:

foreach filter
   previous result
   union all
   select * 
   from data
   where date(day) = date('2021-12-07', filter.offset)

I was thinking of a recursive CTE but I'm not sure how to put it together when another table is involved.

date-arithmetic join date foreach sqlite
1 Answers

What you describe is a join of the 2 tables:

SELECT d.* 
FROM data d INNER JOIN filter f
ON d.name = f.name AND d.day = date('2021-12-07', f.offset);

I added the condition d.name = f.name in the ON clause because of the similarity of the 2 columns which may be related.
Remove the condition if it is not actually needed.

See the demo.

user_104988280
Related