I'm querying results out of 2 tables, at a time, which has a repetitive pattern.
select
abc, xyz
from
2010_Q1_result
inner join
2010_Q1_run
(next comes 2010_Q2... inner join 2010_Q2_run all the way to 2024_Q1_result inner join 2024_Q1_run).
So far I have been changing the year and quarter manually in the query and pulling out results one by one.
How do I automate it to a single query?
You could consider pulling once with a UNION:
select 2010 as year, 'q1' as quarter, abc, xyz
from
2010_Q1_result
inner join 2010_Q1_run
UNION ALL
select 2010 , 'q2' , abc, xyz
from
2010_Q2_result
inner join 2010_Q2_run
UNION ALL
select 2010 , 'q3', abc, xyz
from
2010_Q3_result
inner join 2010_Q3_run
UNION ALL
....
Once written that will spit out every record with columns denoting the year and column. You could toss that whole nasty thing in a view and then just SELECT * FROM my_view_that_I_made so you don't have to write this every time you want to query.