SQL query returns null Mysql

Question

Here is a sql query:

SELECT SUM(a.quantity)-SUM(IF(ISNULL(b.quantity),0,b.quantity)) AS stock 
FROM wp_wpsp_inventory_items a, wp_wpsp_assigned_inventory b 
WHERE a.master_id='9' AND 
     (CASE WHEN ISNULL(b.quantity) 
           THEN '' ELSE a.master_id=b.master_id 
      END)

There are two tables a and b. Table a stores all the inflows and the table b stores all outflows. Here I'm trying to calculate the stock by subtracting outflows from inflows. But there are situations when there is no entry of an Item in Table b is such case the query returns NULL. I tried using IF()and ISNULL() but nothing worked. Please help

A query itself never returns NULL, but an empty result set instead. So what is the real question? Do you ask about how to handle that case in your application? — Nico Haase
– Nico Haase, Commented May 9, 2018 at 9:23
In the above querying I'm querying quantity for master_id=9 but in some cases, my 2nd Table b has no entry for master_id=9 — Waeez
– Waeez, Commented May 9, 2018 at 9:25

pkgajulapalli · Accepted Answer · 2018-05-09 09:30:37Z

1

You should use left outer join.

SELECT SUM(a.quantity - coalesce(b.quantity, 0)) AS stock
FROM wp_wpsp_inventory_items a 
LEFT JOIN
wp_wpsp_assigned_inventory b
ON a.master_id = b.master_id
WHERE a.master_id = '9'

edited May 9, 2018 at 9:30

answered May 9, 2018 at 9:25

pkgajulapalli

1,1663 gold badges22 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Waeez Over a year ago

Syntax Error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a.master_id = b.master_id WHERE a.master_id = '9' LIMIT 0, 25' at line 5

Collectives™ on Stack Overflow

SQL query returns null Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related