CODEWITHSUNDEEP
sql-serverjoin
Ehsan Akbar
Ehsan Akbar

Reputation: 7281

select same columns between two table on different join

I have 2 tables like these:

enter image de

With this condition 

ON dbo.MaterialDescriptions.Id = dbo.Joints.RightMaterialDescriptionId
AND dbo.MaterialDescriptions.Id = dbo.Joints.LeftMaterialDescriptionId

I want to select the itemcode of RightMaterialDescriptionId and LeftMaterialDescriptionId in my query. How can I do that?

Upvotes: 0

Views: 37

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You need to joins the table twice with different aliases:

SELECT rmd.ItemCode as RightItemCode, lmd.ItemCode as LeftItemCode [, other columns...]
FROM dbo.Joints j
JOIN dbo.MaterialDescriptions rmd ON j.RightMaterialDescriptionId = rmd.Id
JOIN dbo.MaterialDescriptions lmd ON j.LeftMaterialDescriptionId = lmd.Id

Note: You might want to use LEFT JOIN instead of INNER JOIN

Upvotes: 2

Related Questions