Microsoft SQL Server Conditional Joining based on 2 columns

Question

I am looking to join 3 tables, all with the same data except one column is a different name (different date for each of the the 3 tables). The three tables look like the following. The goal is if a condition exists in table 1 AND/OR table 2 determine if a condition does or does not exist in table 3 for each individual id/condition. I'm currently left joining table 2 to table 1 but I'm aware that is not accounting for if a condition in table 2 exists that is not in table it is not being accounted for, anyways, any help would into this would be useful.

Table 1
    id  place Condition_2018 
    123  ABC  flu
    456  ABC  heart attack

Table 2
    id  place Condition_2019
    123  ABC  flu
    789  def  copd
Table 3
    id  place Condition_2020
    456  ABC  heart attack
    789  def  copd
    123  ABC  flu
OUTPUT:
Table 2
    id  place Condition_2018  Condition_2019  Condition_2020
    123  ABC  flu             flu             flu
    456  ABC  heart attack    null            heart attack
    789  def  NULL            copd            copd

Thank you!

@Brad . . . Your question could be clearer. For instance, what if the conditions change from year to year? — Gordon Linoff, Feb 21 '20 at 23:19

score 1 · Answer 1 · answered Feb 21 '20 at 23:04

If your database supports full join, you can just do:

select
    id,
    place,
    t1.condition_2018,
    t2.condition_2019,
    t3.condition_2020
from table1 t1
full join table2 t2 using(id, place)
full join table3 t3 using(id, place)

Otherwise, it is a bit more complicated: union all and aggregation is one method:

select 
    id, 
    place, 
    max(condition_2018) condition_2018,
    max(condition_2019) condition_2019,
    max(condition_2020) condition_2020
from (
    select id, place, condition_2018, null condition_2019, null condition 2020 from table1
    union all
    select id, place, null, condition_2019, null from table2
    select id, place, null, null, condition_2020 from table3
) t
group by id, place

I agree on the full join between table1 and table2, but should it be a left join to table3 ("The goal is if a condition exists in table 1 OR table 2 determine if a condition does or does not exist in table 3 for each individual id/condition.")? — Isaac, Feb 21 '20 at 23:14
Thats correct, and match on condition if it does appear in 2018 and/or 2019 and if not null. — Brad, Feb 22 '20 at 16:19

score 1 · Accepted Answer · answered Feb 21 '20 at 23:14

1

How about this (SQL Server syntax)...

SELECT 
    x.id
  , x.place
  , x.Condition_2018
  , x.Condition_2019
  , t3.Condition_2020 
FROM (
        SELECT 
            COALESCE(t1.id, t2.id) AS id
          , COALESCE(t1.place, t2.place) AS place
          , t1.Condition_2018
          , t2.Condition_2019
        FROM Table1 AS t1 
        FULL OUTER JOIN Table2 AS t2 ON t1.id = t2.id AND t1.place = t2.place
    ) AS x LEFT JOIN Table3 AS t3 ON x.id = t3.id AND x.place = t3.place

answered Feb 21 '20 at 23:14

Isaac

3,240
2
24
31

Thanks, I made some modifications and instead of joining on place used t1.condition_2018=t2.condition_2019 and in the left join x.condition_2018 = t3.condition_2020 OR x.condition_2019 = t3.condition_2020 and got exactly what I was looking for. Thank you for your help and everyone elses. – Brad Feb 22 '20 at 17:03

score 0 · Answer 3 · answered Feb 21 '20 at 23:17

You seem to want everything in Table3 and matches in the other two tables. That is just left joins:

select t3.id, t3.place,
       t1.condition_2018, t2.condition_2019, 
       t3.condition_2020
from table3 t3 left join
     table2 t2
     on t3.id = t2.id and t3.place = t2.place left join
     table1 t1
     on t3.id = t1.id and t3.place = t1.place;

score 0 · Answer 4 · answered Feb 22 '20 at 16:23

You need a full outer join of table1 and table2 and a left join to table3:

select 
  coalesce(t1.id, t2.id) id,
  coalesce(t1.place, t2.place) place,
  t1.Condition_2018,
  t2.Condition_2019,
  t3.Condition_2020
from table1 t1 full outer join table2 t2
on t2.id = t1.id
left join table3 t3
on t3.id = coalesce(t1.id, t2.id)

See the demo.
Results:

>  id | place | Condition_2018 | Condition_2019 | Condition_2020
> --: | :---- | :------------- | :------------- | :-------------
> 123 | ABC   | flu            | flu            | flu           
> 456 | ABC   | heart attack   | null           | heart attack  
> 789 | def   | null           | copd           | copd

I like the COALESCE in your join; more concise than my answer. — Isaac, Feb 22 '20 at 18:44

Microsoft SQL Server Conditional Joining based on 2 columns

4 Answers4