Join a query of two tables with another table

Question

I need help to join three tables. Somehow I could join two tables table1 and table2 to get desired output but I want to join another table to get some new related columns. Here is my query:

select 
[from_bus] as [Node], 
[from_bus_id]  as [Node_id]
from table1 
union 
select 
[to_bus] as [Node], 
[to_bus_id]  as [Node_id]
from table1
union
select 
[from_bus] as [Node], 
[from_bus_id]  as [Node_id]
from table2
union 
select 
[to_bus] as [Node], 
[to_bus_id]  as [Node_id]
from table2

Query Output from table1 and table2 :

Node Node_ID
A_22  1
A_11  2
B_33  3
C_25  4

Node and Node_ID are unique. Now, I have another table3 from which I need another column (Zone_ref)which contains IDs of respective Zone.

    table3: 
    Zone   Node_Name  Zone_ref
    A      A_22        1
    A      A_11        1
    B      B_33        3 
    B      B_44        3     
    C      C_31        4
    C      C_25        4

I want to have something like:

Node Node_ID Zone_ref
A_22  1       1
A_11  2       1
B_33  3       3
C_25  4       4

There are common fields where I can join the table but don't know how to integrate one query from two tables with third table. Need your suggestions. By the way I am using access db. Thank you.

Sonal Borkar Sonal Borkar · Accepted Answer · 2018-11-30T13:50:44

Try nested subquery with Join. Like below:

select a.Node, a.Node_id, b.Zone_ref from (
 select 
 [from_bus] as [Node], 
 [from_bus_id]  as [Node_id]
 from table1 
 union 
 select 
 [to_bus] as [Node], 
 [to_bus_id]  as [Node_id]
 from table1
 union
 select 
 [from_bus] as [Node], 
 [from_bus_id]  as [Node_id]
 from table2
 union 
 select 
 [to_bus] as [Node], 
 [to_bus_id]  as [Node_id]
 from table2 ) a
INNER JOIN table3 b ON a.Node = b.Node_Name

Join a query of two tables with another table

1 Answers