Getting Mysql results where subquery is not null

Question

I am working on a query that will get the count of items using a sub query, where the result of both sub queries is not null.

I have this query

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'

Which produces this:

id   | shipped | unshipped
1500 |   NULL  |    2
1501 |   NULL  |    1
1502 |   NULL  |    1
1503 |    1    |    2
1504 |    2    |  NULL
1505 |    2    |    5

I only want results where shipped and unshipped have a positive value. I have tried several things, such as

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S' HAVING SUM(qty) > 0) AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B') HAVING SUM(qty) > 0) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B'

And

SELECT s.id, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') AS shipped, (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) AS unshipped FROM `sales` s WHERE s.status = 'A' OR s.status = 'B' AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND status = 'S') IS NOT NULL AND (SELECT SUM(qty) FROM salesItems WHERE sale_id = s.id AND (status = 'A' OR status = 'B')) IS NOT NULL

Both still return the same results.

I created a DB Fiddle, but it shows no results found, which I ran the same code through MySql and it returns all items

https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/0

question from:https://stackoverflow.com/questions/65890931/getting-mysql-results-where-subquery-is-not-null

Answer 1

Based on @Arganas answer, you can use HAVING

SELECT s.id, 
(
SELECT SUM(qty) 
FROM salesItems 
WHERE sale_id = s.id AND status = 'S'
) AS shipped, 
(
SELECT SUM(qty) 
FROM salesItems 
WHERE sale_id = s.id AND (status = 'A' OR status = 'B')
) AS unshipped 
FROM `sales` s 
WHERE (s.status = 'A' OR s.status = 'B') 
HAVING shipped is not Null and unshipped is not null

this is the fiddle https://www.db-fiddle.com/f/mHuQ4nCHZVPmEacog1iQvq/1