Using a subquery with 3 tables

Question

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

Answer 1

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--

Answer 2

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.

Answer 3

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.

Thanks,

Lance

Answer 4

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.

Answer 5

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.