Using a subquery with 3 tables

Lance James 366 Reputation points
2023-07-19T18:30:32.9333333+00:00

Conventions -
Table A: Release (include if R)

Table B: Comp (include if 0)

Goal: Locate Open Jobs (R) with open operations (0) but getting the operation number that is first after operation Comp = 1 for the given Job and Suffix.

I am stuck on limiting the records to the Goal. I suspect a subquery is involved (could be wrong) but can't get the correct syntax to deliver the correct recordset.

Output desired:

Job	Suffix	Item	Qty	OperNum	WC	Descr
1000	0	AAA	10	30	10	tiny machine
1001	1	BBB	12	60	55	large machine
1002	0	CCC	15	30	20	tinyerest machine
1003	0	AAA	17	40	55	large machine
1005	0	EEE	15	30	20	tinyerest machine
1005	1	EEE	16	60	60	huge machine

Test Data:

CREATE TABLE SyteLine.dbo.A (
  Job		INT,
  Suffix	INT,
  Item		nvarchar(30),
  Qty		INT,
  Release	nvarchar(1)
);
GO

INSERT INTO SyteLine.dbo.A (job, Suffix, Item, Qty, Release)
VALUES  (1000, 0, 'AAA', 10, 'R'),
		(1001, 0, 'BBB', 22, 'R'),
		(1001, 1, 'BBB', 12, 'R'),
		(1002, 0, 'CCC', 15, 'R'),
		(1003, 0, 'AAA', 17, 'R'),
		(1004, 0, 'DDD', 10, 'C'),
		(1005, 0, 'EEE', 15, 'C'),
		(1005, 1, 'EEE', 16, 'R');
GO

CREATE TABLE SyteLine.dbo.B (
    Job		INT,
	Suffix	INT,
	OperNum INT,
	WC		INT,
	Comp	INT
);

GO

INSERT INTO SyteLine.dbo.B (Job, Suffix, OperNum, WC, Comp)
VALUES  (1000, 0, 30, 10, 0),
		(1000, 0, 20, 20, 1),
		(1001, 0, 40, 30, 1),
		(1001, 0, 50, 40, 1),
		(1001, 0, 60, 55, 0),
		(1001, 1, 40, 55, 0),
		(1002, 0, 20, 10, 1),
		(1002, 0, 30, 20, 0),
		(1002, 0, 40, 30, 0),
		(1003, 0, 30, 40, 1),
		(1003, 0, 40, 55, 0),
		(1003, 0, 50, 60, 0),
		(1004, 0, 40, 30, 1),
		(1005, 0, 30, 20, 0),
		(1005, 1, 50, 55, 1),
		(1005, 1, 60, 60, 0);
GO

CREATE TABLE SyteLine.dbo.C (
    WC		INT,
	Descr	nvarchar(30)
);

GO

INSERT INTO SyteLine.dbo.C (WC, Descr)
VALUES  (10, 'tiny machine'),
		(20, 'tinyerest machine'),
		(30, 'small machine'),
		(40, 'medium machine'),
		(55, 'large machine'),
		(60, 'huge machine');

GO

Regards,
Lance
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,275 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. B santhiswaroop naik 370 Reputation points
    2023-07-19T18:39:42.2933333+00:00

    To achieve the desired output, you can use a combination of JOINs and subqueries in your SQL query. Here's the query that will provide you with the records you need:

    SELECT
        a.Job,
        a.Suffix,
        a.Item,
        a.Qty,
        b.OperNum,
        b.WC,
        c.Descr
    FROM
        SyteLine.dbo.A a
    INNER JOIN (
        SELECT
            Job,
            Suffix,
            MAX(OperNum) AS MaxOperNum
        FROM
            SyteLine.dbo.B
        WHERE
            Comp = 1
        GROUP BY
            Job,
            Suffix
    ) b_sub ON a.Job = b_sub.Job AND a.Suffix = b_sub.Suffix
    INNER JOIN SyteLine.dbo.B b ON a.Job = b.Job AND a.Suffix = b.Suffix AND b.OperNum > b_sub.MaxOperNum AND b.Comp = 0
    INNER JOIN SyteLine.dbo.C c ON b.WC = c.WC
    WHERE
        a.Release = 'R';
    
    

    Explanation:

    In the subquery b_sub, we first filter Table B to get the maximum OperNum for each Job and Suffix where Comp = 1.

    Next, we join Table A with the b_sub subquery on Job and Suffix to get the relevant rows from Table A.

    Then, we join Table B with Table A on Job and Suffix and add a condition to get the OperNum that is greater than the MaxOperNum from the b_sub subquery and where Comp = 0.

    Finally, we join Table C to get the Descr based on the WC value from Table B.

    This query will provide you with the desired output, showing the open jobs with open operations and the operation number that comes after the Comp = 1 operation for each job and suffix combination.

    --please don't forget to upvote and Accept as answer if the reply is helpful--


  2. MOHAMMED MOUSSAIF 0 Reputation points
    2023-07-19T18:41:35.6833333+00:00

    Assurez-vous que votre requête est correctement écrite et qu'il n'y a pas d'erreurs de syntaxe. Vérifiez également si vous avez utilisé les bons opérateurs pour accomplir votre objectif.

    Les sous-requêtes peuvent être utiles pour filtrer les enregistrements selon des critères spécifiques. Vous pouvez essayer d'utiliser une sous-requête pour sélectionner les enregistrements qui satisfont votre condition "Comp = 1".

    Si vous avez besoin de combiner les informations de plusieurs tables, assurez-vous que vos jointures sont correctement configurées. Les jointures vous permettent de lier les tables en fonction de colonnes communes.

    Vous pouvez utiliser des clauses de filtrage telles que WHERE ou HAVING pour restreindre les enregistrements retournés par votre requête en fonction de conditions spécifiques.


  3. Lance James 366 Reputation points
    2023-07-19T20:36:18.3+00:00

    Additional information regarding my OP.

    Here is a basic query that gets close but returns too many rows. Only a single row should return where Job and Suffix match.

    SELECT A.Job, A.Suffix, A.Item, B.OperNum, B.WC, C.Descr
    FROM A
    LEFT JOIN B ON A.Job = B.Job
    LEFT JOIN C ON B.WC = C.WC
    WHERE A.Release = 'R' And B.Comp = 0
    ORDER BY A.Job, A.Suffix, A.Item, B.OperNum;
    
    

    Here is the output. I struck the wrong lines.

    User's image

    Thanks,

    Lance

    0 comments No comments

  4. Bruce (SqlWork.com) 52,576 Reputation points
    2023-07-19T21:58:49.7133333+00:00

    you probably wanted inner Jons and to include suffix:

    SELECT A.Job, A.Suffix, A.Item, B.OperNum, B.WC, C.Descr
    FROM A
    JOIN B ON A.Job = B.Job AND A.Suffix = B.Suffix
    JOIN C ON B.WC = C.WC
    WHERE A.Release = 'R' And B.Comp = 0
    ORDER BY A.Job, A.Suffix, A.Item, B.OperNum;
    

    but for table b you have multiple rows that match, for example:

    (1002, 0, 40, 30, 0),
    (1002, 0, 30, 20, 0),
    

    how do you know to pick 30 over 40? is just the min value? then its just:

    SELECT A.Job, A.Suffix, A.Item, B.OperNum, B.WC, C.Descr
    FROM A
    JOIN B ON B.Job = A.Job 
        and B.Suffix = A.Suffix
        and B.Comp = 0
        and B.OperNum = (select min(OperNum)
            from B B2 
            where B2.Job = B.Job
                and B2.Suffix = b.Suffix 
                and B2.Comp = b.Comp)
    JOIN C ON B.WC = C.WC
    WHERE A.Release = 'R'
    ORDER BY A.Job, A.Suffix, A.Item, B.OperNum;
    

    if you meant row insert order, then you need to add row order column, as tables are not ordered by insert.


  5. CosmogHong-MSFT 20,216 Reputation points Microsoft Vendor
    2023-07-20T02:34:55.63+00:00

    Hi @Lance James

    Try this query:

    ;WITH CTE AS
    (
    SELECT A.Job, A.Suffix, A.Item, B.OperNum, B.WC, C.Descr
          ,ROW_NUMBER()OVER(PARTITION BY A.Job, A.Suffix, A.Item ORDER BY B.OperNum) AS RNum
    FROM A
    LEFT JOIN B ON A.Job = B.Job
    LEFT JOIN C ON B.WC = C.WC
    WHERE A.Release = 'R' And B.Comp = 0
    )
    SELECT Job,Suffix,Item,OperNum,WC,Descr
    FROM CTE 
    WHERE RNum = 1
    ORDER BY Job,Suffix,Item,OperNum;
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.