CODEWITHSUNDEEP
sql-server
maX
maX

Reputation: 742

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

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?

Upvotes: 0

Views: 63

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

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

enter image description here

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

Upvotes: 2

Related Questions