Mysql subquery returning null

Question

When I execute the query below, it returns NULL.

SELECT SUM(t.coupon_quantity)
FROM transaction t, coupon c, supplier s 
WHERE MONTH(date_entered) = MONTH(CURDATE()) 
AND  YEAR(date_entered) = YEAR(CURDATE()) 
AND t.coupon_id = c.id
AND c.supplier_id = s.id
AND s.id IN (SELECT us.supplier_id FROM user_supplier us WHERE us.user_id = 4);

When I take out the last line, it returns records, but when I add it, I get nothing so it leads me to believe that it's an issue with my subquery.

When I execute the subquery on it's own, I get two IDs, (14 and 15). And when I replace the subquery with: "AND s.id IN (14,15);" ...it works. Any thoughts?

Don't use implicit (comma-) join syntax. Instead, always use explicit JOIN syntax. — Strawberry, Apr 30 '14 at 22:03

score 0 · Answer 1 · answered Apr 30 '14 at 22:08

If the subquery only returns 14 and 15 with no duplicates, then the following query should work (changing your where in clause with another join)

SELECT SUM(t.coupon_quantity)
FROM transaction t
JOIN coupon c on t.coupon_id = c.id
JOIN supplier s on c.supplier_id = s.id
JOIN user_supplier us on s.id = us.supplier_id and us.user_id = 4
WHERE MONTH(date_entered) = MONTH(CURDATE()) 
AND  YEAR(date_entered) = YEAR(CURDATE())

Unfortunately I don't know why it your IN clause doesn't work when the subquery works by itself.

score 0 · Answer 2 · answered Apr 30 '14 at 22:10

By convention, I generally suggest avoiding comma-join syntax and INNER JOINs.

Try this out and look at your results. They will surely provide some insight into why you aren't getting the desired results

SELECT SUM(t.coupon_quantity)
FROM transaction t
LEFT JOIN coupon c ON t.coupon_id = c.id
LEFT JOIN supplier s ON c.supplier_id = s.id  -- First glance makes me think it should be t.supplier_id?
WHERE MONTH(date_entered) = MONTH(CURDATE())
  AND YEAR(date_entered) = YEAR(CURDATE())
  AND s.id IN (SELECT us.supplier_id FROM user_supplier us WHERE us.user_id = 4);

That will provide you with all transaction records and null values for everything else that doesn't match, so you can more accurately examine your data. When you've corrected the syntax, replace the LEFT with INNER, as necessary.

Mysql subquery returning null

2 Answers2