By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE WORKORDERS (
`ID` INTEGER,
`QUANTITY` INTEGER
);
INSERT INTO WORKORDERS
(`ID`, `QUANTITY`)
VALUES
('1', '2'),
('2', '1');
CREATE TABLE ITEMSINWORKORDERS (
`ID` INTEGER,
`WORKORDER` INTEGER
);
INSERT INTO ITEMSINWORKORDERS
(`ID`, `WORKORDER`)
VALUES
('1', '1'),
('2', '1'),
('3', '2');
SELECT WORKORDERS.ID
, WORKORDERS.QUANTITY AS NOMINAL_QTY
, COUNT(ITEMSINWORKORDERS.WORKORDER) AS ENTERED_QTY
FROM WORKORDERS JOIN ITEMSINWORKORDERS ON
ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
WHERE WORKORDERS.QUANTITY >
( SELECT COUNT(ITEMSINWORKORDERS.WORKORDER )
FROM WORKORDERS INNER JOIN ITEMSINWORKORDERS ON
ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
)
GROUP BY WORKORDERS.ID
, WORKORDERS.QUANTITY
SELECT COUNT(ITEMSINWORKORDERS.WORKORDER )
FROM WORKORDERS INNER JOIN ITEMSINWORKORDERS ON
ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID
| COUNT(ITEMSINWORKORDERS.WORKORDER ) |
|---|
| 3 |