How to select all records from one table that are matching multiple records from second table

Question

Hello, I got lost little bit with this select. It seemed to be easy but not for me anymore... Thank you in advance for any help.

There is a table of JOBS, each JOB has some DrawingVersionID. There is also table DRAWINGS and each Drawing has couple of records in DRAWING VERSIONS.

I need to select all records from JOBS that are based on same DRAWING. Input parameter is DrawingVersionID=3.

It means that first I must search in table DRAWING VERSIONS where DrawingVersionID=3, it gives me DrawingID = 2. Then I must find all the records in DRAWING VERSIONS where DrawingID=2, which are records 3, 4, 5. Then I must search all records in JOBS where DrawingVersionID is 3 or 4 or 5 which will return JOBS 1,3

JOBS
JobID JobName Qty DrawingVersionID
1 Print poster 100 3
2 Print billlboard 150 1
3 Print poster 200 5

DRAWINGS
DrawingID DrawingName
1 Ford Mustang
2 Corvette
3 Ferrari

DRAWING VERSIONS
DrawingVersionID DrawingID VersionName
1 1 Brighter
2 1 Darker
3 2 Brighter
4 2 Lighter
5 2 Smaller

OUTPUT
JobID JobName Qty DrawingID DrawingName
1 PrintPoster 100 2 Corvette
3 PrintPoster 200 2 Corvette

Regards

Petr

Answer 1

Try a query:

declare @parameter int = 3

select j.JobID, j.JobName, j.Qty, dv2.DrawingID, d.DrawingName
from DRAWING_VERSIONS dv1
inner join DRAWING_VERSIONS dv2 on dv2.DrawingID = dv1.DrawingID
inner join JOBS j on j.DrawingVersionID = dv2.DrawingVersionID
inner join DRAWINGS d on d.DrawingID = dv2.DrawingID
where dv1.DrawingVersionID = @parameter
order by j.JobID

Answer 2

Another solution:

DECLARE @DrawingVersionID int = 3;
;WITH CTE_Get_DrawingID AS (
    SELECT DrawingID 
    FROM [DRAWING VERSIONS] 
    WHERE DrawingVersionID = @DrawingVersionID
),
CTE_Get_DrawingVersionID AS (
    SELECT DrawingVersionID, DrawingID 
    FROM [DRAWING VERSIONS] 
    WHERE DrawingID IN (
        SELECT DrawingID 
        FROM CTE_Get_DrawingID
     )
)

SELECT j.JobID, j.JobName, j.Qty, c.DrawingID, d.DrawingName
FROM JOBS AS j
INNER JOIN CTE_Get_DrawingVersionID AS c ON j.DrawingVersionID = c.DrawingVersionID
INNER JOIN DRAWINGS AS d ON c.DrawingID = d.DrawingID;