NULL with JOIN - mysql

Question

i have a specific problem. I want to get data for bills. The data will be used for chart so the sum is needed. Here is the explanation:

Sub1 gets the sum of amount for the bills that were paid this month.

Sub2 gets the sum of amount for the bills that are not paid this month.

Sub3 gets the sum of amount for the bills that are calculated as paid for this month, but they are really not paid. That is in case if other company doesn't pay bill for 5 years or more than this bill is "like" paid (court procedures go after that).

The problem is if I have for example no records for sub1, than sub2 or sub3 can't be joined and I want to show them in any case. In case if sub2 has no records and sub3 has, than sub3 is not shown so that is also the problem. How can I show all the records in one row even if there are NULL values for example for sub1 and NOT NULL for sub 2 and sub3, vice versa. Any help would be appreciated since I am working now on this query solution almost all afternoon. :/

SELECT sub1.y,
       sub1.m,
       sub1.val1,
       sub2.val2,
       sub2.y,
       sub2.m,
       sub3.y,
       sub3.m,
       sub3.val3
FROM
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(paidd)
     AND MONTH(curdate())=MONTH(paidd)
   GROUP BY y,
            m) sub1
INNER JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub2
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub3 
ON sub1.y=sub2.y
AND sub1.m=sub2.m
AND sub2.y=sub3.y
AND sub2.m=sub3.m

possible duplicate of [MySQL FULL JOIN?](http://stackoverflow.com/questions/7978663/mysql-full-join) — Denis de Bernardy, Jun 26 '13 at 13:27

Kickstart · Accepted Answer · 2013-06-26T13:34:03.677

Have an initial select to just get the year / month, then left join the others against it?

SELECT sub0.y,
       sub0.m,
       sub1.val1,
       sub2.val2,
       sub2.y,
       sub2.m,
       sub3.y,
       sub3.m,
       sub3.val3
FROM  (SELECT YEAR(curdate()) AS y, MONTH(curdate()) AS m) Sub0
LEFT JOIN(SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(paidd)
     AND MONTH(curdate())=MONTH(paidd)
   GROUP BY y,
            m) sub1
ON sub0.y=sub1.y
AND sub0.m=sub1.m
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub3 
ON sub0.y=sub3.y
AND sub0.m=sub3.m

Or simplified a touch:-

SELECT sub0.y,
       sub0.m,
       sub1.val1,
       sub2.val2,
       sub3.val3
FROM  (SELECT YEAR(curdate()) AS y, MONTH(curdate()) AS m) Sub0
LEFT JOIN(SELECT YEAR(paidd) AS y,
          MONTH(paidd) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
   GROUP BY y,
            m) sub1
ON sub0.y=sub1.y
AND sub0.m=sub1.m
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
   GROUP BY y,
            m) sub3 
ON sub0.y=sub3.y
AND sub0.m=sub3.m

Or even more simplified

SELECT YEAR(curdate()) AS y, 
        MONTH(curdate()) AS m,
        SUM(IF(paidd IS NOT NULL AND bringbar=0, totwdisc, 0)),
        sub2.val2,
        sub2.val3
FROM  pro_partial_inv
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,   
          SUM(IF(paidd IS NULL AND bringbar=0, netto, 0)) AS val2,
          SUM(IF(paidd IS NOT NULL AND bringbar=0, totwdisc, 0)) AS val3
   FROM pro_partial_inv
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
GROUP BY y, m

great, this solved my problem. now i laugh at myself how i didn't remember to add an extra query for year and month :) — enigmaticus, Jun 26 '13 at 13:35

score 0 · Answer 2 · answered Jun 26 '13 at 13:23

You don't need to use joins for this. You can run 3 separate queries to get the results you want.

SELECT 
    (SELECT SUM(netto) FROM ...) as sub1,
    (...) as sub2,
    (...) as sub3

You will just have to add all the appropriate WHERE clauses on each of the sub-queries.

score 0 · Answer 3 · answered Jun 26 '13 at 13:34

There is no FULL JOIN in MySQL, so you need to left join on a set of appropriate year-month pairs. In your case, there's only one, so:

select ...
from (select year(curdate()) as y, month(curdate()) as m) cur
left join (...) as sub1 on cur.y = sub1.y and cur.m = sub1.m
left join (...) as sub2 on cur.y = sub2.y and cur.m = sub2.m
left join (...) as sub3 on cur.y = sub3.y and cur.m = sub3.m;

NULL with JOIN - mysql

3 Answers3