Can anyone help me when my left join statement? I think the mySQL Engine is broken

Question

Currently I am trying to output thru query the list of Start_Periods in a table called Payslip. This is just a simple query that I am testing out before turning it into something complex. But at this very simple level, there seems to be something wrong on how mySql Engine is displaying my data.

My current query is

   SELECT a.SPERIOD
    FROM Payslip a
    LEFT JOIN Earndetl b ON a.SPERIOD = b.SPERIOD
    LEFT JOIN Ded_detl c ON a.SPERIOD = c.SPERIOD
    WHERE MONTH(a.SPERIOD) = 1
    AND MONTH(b.SPERIOD) = 1
    AND MONTH(c.SPERIOD) = 1
    GROUP BY a.SPERIOD;

Which outputs:

SPERIOD
2015-01-01
2015-01-16

While the following query:

SELECT SPERIOD FROM Payslip WHERE MONTH(SPERIOD) = 1 GROUP BY SPERIOD;

Outputs:

SPERIOD
2015-01-01
2015-01-02
2015-01-16
2015-01-18

Since I am using only Left Join to EarnDetl and Ded_detl, regardless if there is the same SPERIOD in the two tables, this shouldn't be a problem right? Or is there something wrong with my query that I failed to see for the past hour?

Answer 1

When LEFT JOIN, put the right side table's conditions in the ON clause to get true left join behavior! (When in WHERE, you get regular inner join result):

SELECT a.SPERIOD
FROM Payslip a
LEFT JOIN Earndetl b ON a.SPERIOD = b.SPERIOD AND MONTH(b.SPERIOD) = 1
LEFT JOIN Ded_detl c ON a.SPERIOD = c.SPERIOD AND MONTH(c.SPERIOD) = 1
WHERE MONTH(a.SPERIOD) = 1
GROUP BY a.SPERIOD;

If you're not going to select anything else but a.SPERIOD, you can remove the GROUP BY and instead do SELECT DISTINCT.