Mixing ANSI 1992 JOINs and COMMAs in a query

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?

Answers:

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.

Method 1

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 t and m, the final result will be a cartesian product; you might want it to be revised.

I Hope it helped.

Method 2

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!!

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0