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

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.

mysqlsqljoin


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

Thank you for your valuable reply;

I've tried following query and it is working;

    SELECT
    d.millid,
    d.bookno,
    d.partyname,
    d.count,
    d.counttype,
    d.orderqty,
    d.dispqty,
    (d.orderqty - d.dispqty) AS pending
FROM
    (
    SELECT
        b.millid,
        b.count,
        b.counttype,
        b.bookno,
        b.partyname,
        COALESCE(b.bags, 0) AS orderqty,
        Y.bags,
        COALESCE(SUM(Y.bags),
        0) AS dispqty
    FROM
        yarnDispatch Y
    RIGHT JOIN yarnbook b ON
        b.bookno = Y.bookno
    GROUP BY
        b.bookno
    ORDER BY
        b.millid,
        b.bookno
) d
WHERE
    (d.orderqty - d.dispqty) > 0 
ORDER BY
    d.millid,
    d.bookno

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bob Jarvis - Слава Україні
Solution 2
Solution 3 Bharat Duggad

Related Questions