i’m trying the following MySQL query to fetch some data:
SELECT m.*, t.* FROM memebers as m, telephone as t INNER JOIN memeberFunctions as mf ON m.id = mf.memeber INNER JOIN mitgliedTelephone as mt ON m.id = mt.memeber WHERE mf.function = 32
But i always get the following error:
#1054 - Unknown column 'm.id' in 'on clause'
The column does exists and the query works fine with only one table (e.g. when i remove telephone)
Does anybody know what I do wrong?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
According to this link, you shouldn’t mix up both notations when building up joins. The comma you are using to join
memebers as m, telephone as t, and the subsequent calls to
inner join, are triggering the unknown column error.
To deal with it, use
CROSS/INNER/LEFT JOIN instead of commas.
Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression
is interpreted as (t1, (t2 JOIN t3)). This change affects statements
that use an ON clause, because that clause can refer only to columns
in the operands of the join, and the change in precedence changes
interpretation of what those operands are.
For pedagogic purpose, I’m adding the query as it, I think, should be:
SELECT m.*, t.* FROM memebers as m JOIN telephone as t JOIN memeberFunctions as mf ON m.id = mf.memeber AND mf.function = 32 JOIN mitgliedTelephone as mt ON m.id = mt.memeber
Since you’re not joining
m, the final result will be a cartesian product; you might want it to be revised.
I Hope it helped.
It seems your requirement is to join
members table but you are joining with
telephone table. just change their order.
SELECT `m`.*, `t`.* FROM `memebers` AS `m` JOIN `telephone` AS `t` JOIN `memeberFunctions` AS `mf` ON `m`.`id` = `mf`.`memeber` AND `mf`.`function` = 32 JOIN `mitgliedTelephone` AS `mt` ON `m`.`id` = `mt`.`memeber`;
Hope this helps you. Thank you!!