I made a simple query with join but get always this annoying error.
`verk.id`, `verk.date`, `verk.quant`, `verk.verid`, `verk.kunde`, `verk.gebracht`, `loginuser_aqa.name`, `loginuser_aqa.id`
loginuser_aqa.id = verk.verid
verk.gebracht = 0
ORDER BY verk.date;
You have at least two problems in this query.
First, when you use back-ticks to delimit identifiers, you must delimit the table alias separately from the column name.
`verk.id` -- WRONG `verk`.`id` -- CORRECT
The reason is that SQL actually allows you to define column names containing punctuation, white space, etc. if you delimit the column names. So that's what you appear to be doing, requesting a column named
verk.id -- ALSO CORRECT
As others have commented, you don't usually need to use delimited identifiers at all. Use them if your identifiers conflict with MySQL Reserved Words, or if you need to use punctuation, whitespace, or international characters.
The second problem is the MySQL doesn't support
FULL JOIN. It doesn't even recognize
FULL as an SQL keyword. So your query formed like this:
... FROM `verk` FULL JOIN `loginuser_aqa` ON ...
Is interpreted by MySQL as if you had done this:
... FROM `verk` AS `FULL` JOIN `loginuser_aqa` ON ...
In other words, since
AS is an optional keyword in SQL, you have just set
FULL as the table alias for
When you define a table alias, you must use the table alias for any column belonging to that table. You can no longer reference columns as
verk.id, you must use
FULL.id. This part is standard SQL behavior, not a MySQL bug.
Another problem with this is that you aren't getting a
FULL OUTER JOIN in your query result, you're just getting a plain
JOIN which is a synonym for
INNER JOIN. There's no error, but if you needed a full outer join, you won't get the results you expect.
I reported the issue of MySQL not supporting
FULL as a reserved word in 2013: https://bugs.mysql.com/bug.php?id=69858 You can add your vote to the priority of the bug if you want by logging in and clicking the "Affects me" button on that page.
If you need to do a
FULL OUTER JOIN in MySQL, you must use a workaround. See example here: Full Outer Join in MySQL