Another 1064 syntax error

Question

I just can't figure out what's wrong with this syntax. I get a #1064 syntax error at the last line.

SELECT b.id, b.lastname, b.name, c.balance, a.maxdebt, b.warndata, b.warndownload, b.warnupload, b.warndebt, b.cutoffdata, b.cutoffdownload, b.cutoffupload, b.cutoffdebt, b.data, b.download, b.upload, b.warning, b.access, b.cutoffstop
FROM (
SELECT customers.id AS id, SUM(tariffs.value) AS maxdebt
            FROM tariffs
            INNER JOIN assignments ON tariffs.id = assignments.tariffid
            INNER JOIN customers ON assignments.customerid = customers.id
            GROUP BY id
) a
LEFT JOIN (
SELECT customers.id AS id, UPPER(lastname) AS lastname, customers.name AS name, SUM(stats.upload+stats.download) AS data, SUM(stats.download) AS download, SUM(stats.upload) AS upload, cutoffstop, warndata, warndownload, warnupload, warndebt, cutoffdata, cutoffdownload, cutoffupload, cutoffdebt, warning, access
        FROM customers
        LEFT JOIN nodes ON customers.id = nodes.ownerid
        LEFT JOIN stats ON nodes.id = stats.nodeid
        LEFT JOIN customerwarnings ON customers.id = customerwarnings.id
    GROUP BY id
) b
LEFT JOIN (
SELECT customerid, SUM(cash.value) AS balance
        FROM cash
        GROUP BY customerid
) c ON a.id = b.id = c.customerid

I've tried to join the tables with RIGHT and LEFT JOINS to see if there is a difference, but they give me the same error. The different queries within work fine on themselves, but when I join them together in this query I get a syntax error. Does anyone have an idea what I should or shouldn't do?

The error: "#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 21"

I think in subquery `b` you need to change the `GROUP BY` because you use aggregation `SUM` but select more columns than the `GROUP BY` — JScoobyCed, Oct 25 '13 at 10:54

Naveen Kumar Alone · Accepted Answer · 2013-10-25T11:01:50.110

Here is the reference for Multiple LEFT JOIN

SELECT b.id, b.lastname, b.name, c.balance, a.maxdebt, b.warndata, b.warndownload, b.warnupload, b.warndebt, b.cutoffdata, b.cutoffdownload, b.cutoffupload, b.cutoffdebt, b.data, b.download, b.upload, b.warning, b.access, b.cutoffstop
FROM (
SELECT customers.id AS id, SUM(tariffs.value) AS maxdebt
            FROM tariffs
            INNER JOIN assignments ON tariffs.id = assignments.tariffid
            INNER JOIN customers ON assignments.customerid = customers.id
            GROUP BY id
) a
LEFT JOIN (
SELECT customers.id AS id, UPPER(lastname) AS lastname, customers.name AS name, SUM(stats.upload+stats.download) AS data, SUM(stats.download) AS download, SUM(stats.upload) AS upload, customers.cutoffstop, warndata, warndownload, warnupload, warndebt, cutoffdata, cutoffdownload, cutoffupload, cutoffdebt, nodes.warning, nodes.access
        FROM customers
        LEFT JOIN nodes ON customers.id = nodes.ownerid
        LEFT JOIN stats ON nodes.id = stats.nodeid
        LEFT JOIN customerwarnings ON customers.id = customerwarnings.id
    GROUP BY id
) b ON a.id = b.id
LEFT JOIN (
SELECT customerid, SUM(cash.value) AS balance
        FROM cash
        GROUP BY customerid
) c ON a.id = c.customerid

Ah great! That second option indeed worked. I'm wondering why actually? If you'd like to explain a bit? — Jelle, Oct 25 '13 at 11:03
@user2886735 it is called multijoin. here is a bit more clarification here http://stackoverflow.com/questions/2366780/how-to-do-an-inner-join-on-multiple-columns/2366820#2366820 — Naveen Kumar Alone, Oct 25 '13 at 11:04
@user2886735 joining is made upon the columns where by using ON clause. :) — Naveen Kumar Alone, Oct 25 '13 at 11:06

Another 1064 syntax error

1 Answers1