mitdaniels
asked on
Join on the same table different column
Is it possible to JOIN on the same table, but different column in the same query?
SELECT Table1.Name, Table2.FirstName
FROM Table1
INNER JOIN Table2
ON Table1.Table1ID = Table2.FirstID
INNER JOIN Table2
ON Table1.Table1ID = Table2.SecondID
I know the above scenario doesn't work, so how could I JOIN on the same table different column?
SELECT Table1.Name, Table2.FirstName
FROM Table1
INNER JOIN Table2
ON Table1.Table1ID = Table2.FirstID
INNER JOIN Table2
ON Table1.Table1ID = Table2.SecondID
I know the above scenario doesn't work, so how could I JOIN on the same table different column?
ASKER
Thanks aneeshattingal.
I gave it a go, but get this error message:
"Msg 1013, Level 16, State 1, Line 1
The objects "Table2" and "Table2" in the FROM clause have the same exposed names. Use correlation names to distinguish them."
I gave it a go, but get this error message:
"Msg 1013, Level 16, State 1, Line 1
The objects "Table2" and "Table2" in the FROM clause have the same exposed names. Use correlation names to distinguish them."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The concept works thanks!!
SELECT Table1.Name, Table2.FirstName
FROM Table1
INNER JOIN Table2
ON Table1.Table1ID = Table2.FirstID
LEFT JOIN Table2
ON Table1.Table1ID = Table2.SecondID