sql error 1064 when i change inner join to left join

Question

i write sql

SELECT 
    count(*) as count,g.name
FROM
    test_scale g
        INNER JOIN
    test_user_scale_result a
        INNER JOIN
    en_org_user_department b
        INNER JOIN
    en_org_department d
        INNER JOIN
    test_scale_type e ON a.user_id = b.user_id
        AND g.id = a.scale_id
        AND b.department_id = d.id
        AND d.position LIKE CONCAT((SELECT 
                    position
                FROM
                    en_org_department
                WHERE
                    id = 8),
            '%')
        AND e.id = g.scale_type_id
        and b.status = 1
        and g.scale_type_id IN (1 , 9)
        and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45'
        group by a.scale_id;

i run correctly. but when i change inner to left,like

SELECT 
    count(*) as count,g.name
FROM
    test_scale g
        left JOIN
    test_user_scale_result a
        left JOIN
    en_org_user_department b
        left JOIN
    en_org_department d
        left JOIN
    test_scale_type e ON a.user_id = b.user_id
        AND g.id = a.scale_id
        AND b.department_id = d.id
        AND d.position LIKE CONCAT((SELECT 
                    position
                FROM
                    en_org_department
                WHERE
                    id = 8),
            '%')
        AND e.id = g.scale_type_id
        and b.status = 1
        and g.scale_type_id IN (1 , 9)
        and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45'
        group by a.scale_id;

it error 20:37:15 SELECT * FROM test_user_scale_result a
left JOIN en_org_user_department b left JOIN
en_org_department d left JOIN test_scale_type e left JOIN test_scale g ON a.user_id = b.user_id AND g.id = a.scale_id AND b.department_id = d.id AND d.position LIKE CONCAT((SELECT position FROM en_org_department WHERE id = 8),
'%') AND e.id = g.scale_type_id and b.status = 1
and g.scale_type_id IN (1 , 9) and a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45' Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 26 0.0060 sec

Answer 1

You should put the join conditions in the proper ON clauses, not all together in one final ON, like this:

SELECT      count(*) as count,
            g.name
FROM        test_scale g
LEFT JOIN   test_user_scale_result a
        ON  g.id = a.scale_id
        AND a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45'
LEFT JOIN   en_org_user_department b
        ON  a.user_id = b.user_id
        AND b.status = 1
LEFT JOIN   en_org_department d
        ON  b.department_id = d.id
        AND d.position LIKE CONCAT(
                (SELECT position
                FROM    en_org_department
                WHERE   id = 8),
                '%')
LEFT JOIN   test_scale_type e
        ON  e.id = g.scale_type_id
WHERE       g.scale_type_id IN (1, 9)
GROUP BY    g.name;

The condition on the g.scale_type_id should appear in the WHERE clause, unless you really want to have other records included.

Note also, that it is advised to group by g.name, since that is the column you SELECT for having the related count. In Standard SQL it is mandatory to GROUP BY the non-aggregated columns that you have in your SELECT list.

Answer 2

I think left join requires an on clause immediately following the join. Really, you should always be doing this anyway:

SELECT 
    count(*) as count,g.name
FROM
    test_scale g left JOIN
    test_user_scale_result a
    ON g.id = a.scale_id AND a.create_time BETWEEN '2015-01-07 18:09:45' and '2015-11-09 18:09:45' left JOIN
    en_org_user_department b
    ON a.user_id = b.user_id AND b.status = 1 left JOIN
    en_org_department d
    ON b.department_id = d.id AND
       d.position LIKE CONCAT((SELECT position FROM en_org_department WHERE id = 8),
                              '%') left JOIN
    test_scale_type e
    ON e.id = g.scale_type_id
WHERE g.scale_type_id IN (1 , 9)
group by a.scale_id;

Note:

• I'm pretty sure the conditions on the particular tables need to go in the ON clause where that table first appears. I don't think the left join will work if all of them are stuffed in the final on clause.
• The conditions on the first table should be in a where clause. Otherwise, you'll get strange results.
• Here are some simple rules when using joins. (1) Always have an ON clause follow every JOIN, except for a CROSS JOIN. (2) Never use commas in the FROM clause (you aren't doing this, but I always throw this in).