Include a column from another table through multiple relationships

Transact-SQL
1

I've been trying joins but not getting the result I want...

I want to include 2 columns from tblA and one from tblC but the two tables do not have a direct relationship.

The result should display tblA.ID, tblA.CREATION_DATE, tblC.BL_NAME.

See the below mapping tblA.BL_ENTITY_ID > tblB.ID and tblB.BL_VERSION > tblC.ID

It should be one to one mapping i.e. one BL_NAME per tblA.ID

relationships
relationships2445×735 21.7 KB

I've only ever wrote simple SELECT queries so this one has me stumped... I'm pretty sure it is a JOIN I am looking for or a JOIN of a JOIN?

Thanks!

2

Can you try this and looks like the table design needs a change.
Normally, you would have a primary key on the ID column which would be referenced into another table as a foreign key.

you may get better solutions from experts here.

select tblA.ID,
tblA.CREATION_DATE,
tblC.BL_NAME
from tblA
inner join tblB
on tblA.BL_ENTITY_ID=tblB.ID
inner join tblC
on tblB.BL_VERSION =tblC.ID

3

Brilliant! Thanks so much!

This gave me enough to tweak and resolve.

I'm extracting from a 3rd party DB that we have no control over table design.