Bcktr Bcktr - 2 months ago 4
SQL Question

Is possible Joining in same table with same coloumn in SQL?

I Have 3 table, that the one of them is information center table assumed table 1.

Table1 as (tank information)

-----------------------------------------
Prikey TankName
-----------------------------------------
24 Tank1
25 Tank2


Table2 as (source)

-----------------------------------------
ID Tank Source ID Opotional
-----------------------------------------
1 25 Source
2 24 test
etc


Table3 as (Destination)

---------------------------------------------------
Tank Destination ID Opotional ID Source
---------------------------------------------------
25 Destination 1
24 Destination 2
etc


I would like to get the "tank name" in twice and display the "Source" and "destination" with match ID of 'Tank'. So both of them having Tank ID that I use on one query. I have tried joined but is crashed. Is it possible?

Hope the result is :

--------------------------------------------------------------------------------------------------
Source (tank Name) | Destination (Tank Name) | opotional (source) | Opotional (Destination)
--------------------------------------------------------------------------------------------------

Answer

You just need to use alias, Both on the tables that you join more than once (Table1) and on the fields that you return more than once (TankName, Optional).

Something like:

select Source.TankName as SourceTank, Destination.TankName as DestinationTank, 
       Table2.Optional as SourceOptional, Table3.Optional as DestionationOptional
from Table2
     inner join Table3 on Table3.ID = Table2.ID
     inner join Table1 as Source on Source.PriKey = Table2.TankID
     inner join Table1 as Destination as Destination.PriKey = Table3.TankID
Comments