Joining 2 Tables Gives Null Result-CodePudding

I want to combine these two tables.

Sales Table

Expense Table

Here is my code to combine them:

SELECT sl.month, sl.sumnet, ex.sumexp, (sl.sumnet-ex.sumexp) AS profit
FROM
    (SELECT date, DATE_FORMAT(date,'%m%Y') AS date_id, 
            DATE_FORMAT(date,'%b') AS month, 
            YEAR(date) AS year, SUM(net_sales) AS sumnet 
    FROM sales 
    GROUP BY month) sl
LEFT JOIN 
        (SELECT date, DATE_FORMAT(date,'%m%Y') AS date_id, 
                DATE_FORMAT(date,'%b') AS month, 
                YEAR(date) AS year, SUM(total) as sumexp 
        FROM expense 
        GROUP BY month) ex 
    ON sl.date_id = ex.date_id 
WHERE sl.year = '2021'
ORDER BY sl.date_id ASC

I wanted to show results for the year 2021 only but the November shows NULL for the sumexp and profit columns (see below). How do I fix it?

CodePudding user response：

Posible answer, you must take care of nulls

select
 s.date_id,
 s.`month` ,
 s.`year` ,
 sum(s.sumnet),
 sum(distinct coalesce(e.sumexp,0)),
 sum(s.sumnet) - sum(distinct coalesce(e.sumexp,0)) 
from sales as s
left join expanse as e on s.date_id = e.date_id and s.`month` = e.`month` and s.`year` = e.`year`
group by date_id, `month` , `year`;

And filter by your desire.

CodePudding user response：

TRY

SELECT sl.month, sl.sumnet, ex.sumexp, (sl.sumnet-ex.sumexp) AS profit
FROM
    (SELECT date, DATE_FORMAT(date,'%m%Y') AS date_id, 
            DATE_FORMAT(date,'%b') AS month, 
            YEAR(date) AS year, SUM(net_sales) AS sumnet 
    FROM sales 
    WHERE year='2021'
    GROUP BY month) sl
LEFT JOIN 
        (SELECT date, DATE_FORMAT(date,'%m%Y') AS date_id, 
                DATE_FORMAT(date,'%b') AS month, 
                YEAR(date) AS year, SUM(total) as sumexp 
        FROM expense 
        WHERE year='2021'
        GROUP BY month) ex 
    ON sl.date_id = ex.date_id 
ORDER BY sl.date_id ASC

you have filtered the year only for one table so the other one is showing null with multiple value.