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
CodePudding user response:
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.