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?

jarlh · Accepted Answer · 2016-04-22T10:56:46.993

4

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.

edited Apr 22 '16 at 10:56

answered Apr 22 '16 at 10:55

jarlh

42,561
8
45
63

Perhaps a bit of explanation wouldn't go amiss. – HoneyBadger Apr 22 '16 at 10:56
@jarlh thank you for your answer, So does this mean that when I left join, all joined tables filters shouldn't be placed in the WHERE Statement? – Akira Hora Apr 22 '16 at 11:09
@AkiraHora the query first does the WHERE then the JOIN. that's the way SQL Work, first it filters the data then joins it. – CiucaS Apr 22 '16 at 11:11
@AkiraHora, Correct! Leave Payslip's condition in WHERE, and put the rest of them in the ON clauses. – jarlh Apr 22 '16 at 11:11
@jarlh Thanks. I'm quite satisfied with this. – Akira Hora Apr 22 '16 at 11:15

score 0 · Answer 2 · answered Apr 22 '16 at 11:08

0

Thought id do it with the with clause just for a bit of fun.

WITH
A as (
    SELECT * FROM Payslip WHERE MONTH(SPERIOD) = 1
),
B as (
    SELECT * FROM Earndetl WHERE MONTH(SPERIOD) = 1
),
C as (
    SELECT * FROM Ded_detl WHERE MONTH(SPERIOD) = 1
)

SELECT a.SPERIOD
FROM A
LEFT JOIN B ON A.SPERIOD = B.SPERIOD
LEFT JOIN C ON A.SPERIOD = C.SPERIOD
GROUP BY a.SPERIOD;

answered Apr 22 '16 at 11:08

CathalMF

9,705
6
70
106

This is actually quite interesting, a bit off topic to the question, I am creating this query to place it to Jasper reports, I wonder if I could use this Query despite having multiple statements. – Akira Hora Apr 22 '16 at 11:11
Actually just noticed you're using MySQL. Not sure if it supports WITH. Im just checking now. – CathalMF Apr 22 '16 at 11:12
1

SQL is such fun, you can acheive the same in so many ways! (E.g. a left join can be done with an inner join unioned with a not exists.) – jarlh Apr 22 '16 at 11:14
@AkiraHora I cant find anything about whether it supports the WITH clause or not. You can just try it and see. – CathalMF Apr 22 '16 at 11:15
@CathalMF Thanks for the idea. I'll do my own research on this. ;) – Akira Hora Apr 22 '16 at 11:16

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

2 Answers2