How to join one column multiple times to many columns? – SQL

I have two tables like:

t1:

| parent  | main | child
| ------- | ---- | ------
| A       | B    | NULL
| B       | C    | NULL
| NULL    | C    | D

t2:

| letter  | num  |
| ------- | ---- |
| A       | 1    |
| A       | 2    |
| B       | 7    |
| C       | 12   |
| D       | 14   |

I want an output like:

output:

| parent  | parent_num | main   | main_num | child | child_num
| ------- | ---------- | ------ | -------- | ----- | ---------
| A       | 1          | B      | 7        | NULL  | NULL
| A       | 2          | B      | 7        | NULL  | NULL
| B       | 7          | C      | 12       | NULL  | NULL
| NULL    | NULL       | C      | 12       | D     | 14

I think this will require JOIN and UNION ALL, but I can’t figure out how exactly to put everything together. I’m confused because we’ll need to join multiple times per row.

Would appreciate any help, thanks

Answer

You can do:

select
  t1.parent, p.num as parent_num,
  t1.main, m.num as main_num,
  t1.child, c.num as child_num  
from t1
left join t2 p on p.letter = t1.parent
left join t2 m on m.letter = t1.main
left join t2 c on c.letter = t1.child

However, I would say the cardinalities of those relationships look sketchy. I would revise the database model design.