SQL LEFT JOIN to table determied by result from inner SELECT

Question

I'm just wondering if this is possible. I'm trying show all results from one table and grab more details from respective group(table)

+--------------------------+     +--------------------------+
| table group_a            |     | table group_b            |
+--------------------------+     +--------------------------+
| id      name        age  |     | id      name        age  |
| s01     John        10   |     | s11     Clark       11   |
| s02     Jane        11   |     | s12     Cherry      09   |
+--------------------------+     +--------------------------+

+----------------------------+
| table result_1             |
+----------------------------+
| id      result     group   |
| s01     9          a       |
| s12     10         b       |
| s11     9          b       |
| s02     7          a       |
+----------------------------+

I was hoping to get this output

  id      name     age     result
+------------------------------------+
  s12     Cherry   09      10
  s01     John     10      9
  s11     Clark    11      9
  s02     Jane     11      7

I'm kind of stuck on how to point my query to different tables. Anyway I just want to know if this is possible or I should go for different approach.

B'rgrds,

score 1 · Accepted Answer · answered May 11 '21 at 10:59

You can use left join twice:

select r.id, coalesce(a.name, b.name) as name, coalesce(a.age, b.age) as age,
       r.result
from result_1 r left join
     group_a a
     on r.group = 'a' and r.id = a.id left join
     group_b b
     on r.group = 'b' and r.id = b.id;

I would expect the above to have the better performance, but you can also use union all before the join:

select r.id, ab.name, ab.age, r.result
from result_1 r left join
     ((select id, name, age, 'a' as group
       from group_a a
      ) union all
      (select id, name, age, 'b' as group
       from group_b b
      )
     ) ab
     on r.group = ab.group and r.id = as.id ;

Note: It is better to have all the groups in a single table rather than splitting them among multiple tables.

SQL LEFT JOIN to table determied by result from inner SELECT

1 Answers1