no Sql data returned even though it exists-CodePudding

Here is my sqlquery :

SELECT *
FROM work_orders
WHERE LineKey = 'ABC' 
AND StateKey  = 'Approved' 
AND orderKey = 'Released' 
AND WOID NOT IN (
    SELECT WOID 
    FROM serialized_assembly
    GROUP BY (WOID)
    HAVING COUNT(WOID) > 9
    )   
LIMIT 1

Which does not return any data even though there's an WOID 1234 that passes all these conditions.

When I break this query into two parts: part1)

SELECT *
FROM work_orders
WHERE LineKey = 'ABC' AND StateKey  = 'Approved' AND orderKey = 'Released'

I can see WOID 1234 in my results

part2)

SELECT WOID 
FROM serialized_assembly
GROUP BY (WOID)
HAVING COUNT(WOID) > 9

I can see that WOID 1234 is not a part of that group in the results

However, when I combine those two, I get nothing. Is there anything wrong with the sql query ?

CodePudding user response：

NOT IN (null) is probably the problem here, because that will never be TRUE.

You can use NOT EXISTS instead, or compare the count:

SELECT *
FROM work_orders
WHERE LineKey = 'ABC' 
AND StateKey  = 'Approved' 
AND orderKey = 'Released' 
AND (SELECT COUNT(WOID)
     FROM serialized_assembly
     WHERE work_orders.WOID = serialized_assembly.WOID) <= 9
LIMIT 1