how to write SQL query to loop across multiple tables or maybe use recursive query

When I have 1 item in tmp_lookup my results are fine. When I have 1 + item in lookup table the results are not as expected, there are some double records. 'Tmp_pat1' table creates record for combination of person_id and concept_id which I am joining in tmp_pat_km_format table and we use the concept name from tmp_pat1 to fill if they are missing in tmp_pat_freq table.

The problem is now since there are two records for each person_id, concept_id combo, both are getting added. If I can do 1 conceptid at a time it would work. So if I do a loop and look at one concept id at a time it would work. I would need to union the results.

For example first loop would have just prednisone in tmp_lookup and we are only using records from tmp_pat_freq with that concept id. Second loop would have amoxicillin. Then the results table would be Union of both tmp_pat_km_format. I am aware that loop is not common in sql. That is why I am thinking there must be a better way to do this, maybe recursive query but I am stuck.

select distinct concept_id, concept_name from tmp_lookup ;

--1551099 prednisone

--1713332 amoxicillin

Drop table if exists tmp_pat1;
create temp table tmp_pat1 as                       
select a.person_id, b.* from tmp_pat a join (select  distinct concept_id, concept_name from tmp_lookup ) b on 1 = 1;

Drop table if exists tmp_pat_km_format;
create temp table tmp_pat_km_format as
  select
  ROW_NUMBER() Over() as id2
  ,a.grp
  ,a.gender
  ,a.age_group
  ,c.w_numpat*1.000/c.u_numpat as weight
  ,case when d.concept_name is not null then d.concept_name else e.concept_name end as concept_name
  ,case when b.days_to_first_visit is not null then b.days_to_first_visit else a.followup_days_observed end as days_observed
  ,case when b.days_to_first_visit is not null then 1 else 0 end as had_event
  ,case when num_baseline_dates = 0 then 1 else 0 end as naive_pat
  from
  tmp_pat a
  left join (select * from tmp_pat_freq where visit_type = 'All') b on a.person_id = b.person_id
  left join tmp_pat_weights c on a.age = c.age and a.gender = c.gender and a.grp = c.grp
  left join (select distinct concept_id,concept_name from tmp_lookup) d on b.concept_id = d.concept_id
  left join tmp_pat1 e on a.person_id = e.person_id;

There are a whole bunch of red flags I see here. Joel Coehoorn mentioned in a comment that you are doing it wrong if you are looking to do a loop in SQL. I noticed that you are creating a 'temp' table that is really just another table. It isn't using a local, global or variable table, it is going to be present in your DB just like any other sql table. If there is any chance of this script running concurently you are probably going to run into some really messy problems.

But, you aren't aksing for a crtique of what you are doing. You can create loops in SQL using cursors. I am hesitant to suggest this as a solution, as they are almost always a poor choice, causing scalling and performance issues like nobody's business. Still, if you are trying to loop in SQL, cursors can do this. You have been warned.

If you are thinking about SQL like you think about imperative programming languages, you are misunderstanding how it works.SQL is more designed around set theory, where you are concerned with inclusion or exclusion in a set and not how the set is built.