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

petr p 41 Reputation points
2022-04-28T11:39:04.65+00:00

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

0 comments No comments
{count} votes

Accepted answer
  1. Viorel 95,071 Reputation points
    2022-04-28T11:54:34.597+00:00

    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
    

1 additional answer

Sort by: Most helpful
  1. Guoxiong 8,076 Reputation points
    2022-04-28T13:57:56.757+00:00

    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;
    
    0 comments No comments