MYSQL not getting the sum zero values (having no entries of that id) against distinct id of another-CodePudding

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(y.bags),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from yarnDispatch y 
       JOIN yarnbook b ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno

In this query, table yarnbook is master table, having unique bookno (Booking Nos) from 1 to 2600 (and on) and other table yarndispatch have several or zero dispatch (bags) entries against the each of the same bookno (yarnbook).

When I'm running this query, giving the correct result where there is atleast single dispatch entry against bookno, but not giving (sum(bags) as 0) where there is no entry of dispatch in yarndispatch table.

Please share your views/solution.

CodePudding user response：

You need to perform an outer join, which preserves data in the case where one table has data and the other table does not, instead of an inner join which only returns rows if data is present in both tables.

Saying JOIN is equivalent to INNER JOIN. Replace JOIN with LEFT OUTER JOIN and your results should be more in line with what you want.

CodePudding user response：

You need a LEFT JOIN or RIGHT JOIN to get the "empty" rows

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(IFNULL(y.bags,)),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from yarnDispatch y 
       RIGHT JOIN yarnbook b ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno

Both joins are interchangeble but some peolple prefer LEFT JOIN

SELECT d.millid, d.bookno, d.partyname, d.count, 
       d.counttype, d.orderqty, d.dispqty, d.pending 
from ( SELECT b.millid, b.count, b.counttype, b.bookno, b.partyname, 
              COALESCE(b.bags,0) as orderqty, 
              COALESCE(sum(IFNULL(y.bags,0)),0) as dispqty, 
              COALESCE((b.bags-sum(y.bags)),0) as pending 
       from
       yarnbook b 
        LEFT JOIN yarnDispatch y ON b.bookno=y.bookno 
       GROUP by b.bookno) d 
WHERE d.pending>0 
ORDER BY d.millid, d.bookno