SQL Server: query to get data from both same table and another table

Question

I have 3 tables

contract master

ContractId	contract Name	PlanCode	parentcontractId	IsCompleted
1	contract 01	master plan 01	0	true
2	contract 01	exterior plan 01	1
3	contract 01	interior plan 01	1
4	contract 02	master plan 02	0	true
5	contract 02	exterior plan 02	4
6	contract 02	interior plan 02	4

contractsupplierassoc

ContractId	ContractorId
1	22
4	33

contractordata

ContractorId	Name
22	acme contractor
33	real contractor

I want a query which gives me result like

exterior plan code	interior plan code	Contractor name
exterior plan 01	interior plan 01	acme contractor
exterior plan 02	interior plan 02	real contractor

How do I do it?

So far what I have done is to get the interior and exterior plancodes on single table

select a.PlanCode, b.PlanCode 
from contractmaster a 
join contractmaster b on a.ContractId = b.ParentContractId 
where a.IsCompleted = 1

How do I join the contractordata using the contractsupplierassoc?

How do you decide which is the exterior and which the interior code? Do we just assume PlanCode starts with exterior or interior respectively? — Charlieface
– Charlieface, Commented Jul 25, 2021 at 1:30

Doug Coats · Accepted Answer · 2021-07-24 13:49:19Z

2

Here you go, you might have to alter based on what your objects actually look like. This join path was actually quite simple.

Here's a real bad visual aid for you

select 
    cm.PlanCode
    ,b.PlanCode 
    , cd.[Contractor name]
from 
    contractmaster cm 
    join contractmaster b on cm.ContractId=b.ParentContractId 
    join contractsupplierassoc csa ON cm.ContractID = csa.ContractID
    join contractordata cd ON csa.ContractorId = cd.ContractorId
where 
    a.IsCompleted=1

answered Jul 24, 2021 at 13:49

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Cappelletti Over a year ago

The visual was just fine :)

Collectives™ on Stack Overflow

SQL Server: query to get data from both same table and another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related