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?

回答1:

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

回答2:

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.