My Query with a sub-query won't return an empty set-CodePudding

I have two tables in MySQL:

Table 1 - WORKORDERS

ID	QUANTITY
1	2
2	1

Table 2 - ITEMSINWORKORDERS

ID	WORKORDER
1	1
2	1
3	2

I have a query:

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 
        )

Originally, I had a left outer join in my statement, so I switched it to inner in hopes of getting an empty set. Anyone have any recommendations on making it return an empty set whenever there are no workorders with missing entries? which is intended to find all workorders where not all items have been entered, and the nominal quantity, entered in the WORKORDERS table is greater than the number of records in ITEMSINWORKORDERS corresponding to that workorder. I expected to return an Empty Set. Instead, I get

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	0

Originally, I had a left outer join in my statement, so I switched it to inner in hopes of getting an empty set.

Addendum: I tried solving this myself using NULLIF thus:

SELECT WORKORDERS.ID
    , WORKORDERS.QUANTITY AS NOMINAL_QTY
    , NULLIF(COUNT(ITEMSINWORKORDERS.WORKORDER), 0) 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 
    )

what I got was frustrating:

ID	NOMINAL_QTY	ENTERED_QTY
NULL	NULL	NULL

CodePudding user response：

I don't know what you expoect as your qiery delivers no rows.

But for count to work you need a GROUP BY

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

ID | NOMINAL_QTY | ENTERED_QTY
-: | ----------: | ----------:

SELECT COUNT(ITEMSINWORKORDERS.WORKORDER ) 
          FROM   WORKORDERS INNER JOIN ITEMSINWORKORDERS ON 
                    ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID

| COUNT(ITEMSINWORKORDERS.WORKORDER ) |
| ----------------------------------: |
|                                   3 |

db<>fiddle here