Table Self join

Question

Table_a has columns : old_id and new_id . Following query gives planning region and organization code for old_id.

 SELECT   a.old_id,d.planning_region,b.organization_code 
            FROM  table_a  a 
                     INNER JOIN table_b b ON a.old_id = b.organization_id
                     INNER JOIN table_c c ON c.organization_code = b.organization_code
                     INNER JOIN table_d d ON d.planning_location_id = b.organization_code  

My requirement is get organization code for new_id too. So my output will be like this

old_id, planning_region ( of old_id ), organization_code (of old_id ) and organization_code (of new_id ).

Self Join should work but here in this case, Do I need to do self join of all 4 tables ?

Note: new_id also can be joined same as old_id with table_b.

Answer 1

If I am understanding correctly, you can add more joins.

If you just want the new organization_code:

SELECT 
    a.old_id,
    d.planning_region,
    b.organization_code,
    b1.organization_code organization_code_new
FROM  table_a  a 
INNER JOIN table_b b  ON a.old_id = b.organization_id
INNER JOIN table_c c  ON c.organization_code = b.organization_code
INNER JOIN table_d d  ON d.planning_location_id = b.organization_code  
INNER JOIN table_b b1 ON a.new_id = b1.organization_id

If you also want the planning_region, then we need to bring d as well:

SELECT 
    a.old_id,
    d.planning_region,
    b.organization_code, 
    d1.planning_region planning_region_new,
    b1.organization_code organization_code_new
FROM  table_a  a 
INNER JOIN table_b b  ON a.old_id = b.organization_id
INNER JOIN table_c c  ON c.organization_code = b.organization_code
INNER JOIN table_d d  ON d.planning_location_id = b.organization_code  
INNER JOIN table_b b1 ON a.new_id = b1.organization_id
INNER JOIN table_c c1 ON a.new_id = c1.organization_id
INNER JOIN table_d d1 ON d1.planning_location_id = b1.organization_code

Side note: it is not obvious what the purpose of table c is in the query (apart, maybe, filtering?).