79682219

Date: 2025-06-27 16:18:35
Score: 0.5
Natty:
Report link

Ok the question revealed the answer (clarifying that NAME is not dimensional). The solution that seems most clear is something like the following. Note I'm also joining another table D that only joins on A.ID to demonstrate it must come after the joins on B,C.

Please scrutinize.

with NAME as (
  select distinct A_ID, NAME from B
  union
  select distinct A_ID, NAME from A
)
select distinct a.ID as 'A_ID', b.NAME as 'B_NAME', c.NAME as 'C_NAME', B.etc., C.etc., D.etc.
from A a
  inner join NAME n on n.A_ID = a.ID
  full join B on a.ID = b.A_ID and n.NAME = b.NAME
  full join C on c.ID = c.A_ID and n.NAME = c.NAME and (b.NAME = c.NAME or b.NAME is null)
where (b.NAME is not null or c.NAME is not null)
Reasons:
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: KSa2