Mohsin Intazar Mohsin Intazar -4 years ago 78
SQL Question

Multiple values from Joining three tables

I have to get A.Ref_id, B.Ref_id and B_Id's Ref_id

Table A_B having Column_A_ID and Column_B_ID
Table A having ID, Ref_id, Name, B_Id (This is the B.ID from table B)
Table B having ID, Ref_id, Name


Currently I'm having the following query

SELECT A.Ref_id as A_Ref_Id, B.Ref_id as B_Ref_Id, B_Id
FROM A_B
JOIN A on A_B.Column_A_ID = A.Id
JOIN B on A_B.Column_B_ID = B.Id
JOIN B AS Main_B on B.id = A.B_id;


By this query I'm getting the
A.Ref_Id
and
B.Ref_Id
columns correctly as they are showing their relevant
Ref_id
but for
B_Id
I want to have the
Ref_id
and it is showing the
B.id
instead.

Answer Source

You want this (right?): The value of B.Ref_id in the row where B.ID = A.B_Id while this row in turn must have A.ID=A_B.Column_A_ID. And the whole thing for every row in A_B:

SELECT A.Ref_id as A_Ref_id, B.Ref_id as B_Ref_id, Main_B.Ref_id
FROM A_B
JOIN A ON A_B.Column_A_ID = A.Id 
JOIN B ON A_B.Column_B_ID = B.Id 
JOIN B AS Main_B on Main_B.id = A.B_id;

(Last line of code with the important correction which was proposed by @Mojtaba)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download