James Black <[EMAIL PROTECTED]> wrote on 11/21/2005 09:39:32 AM:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Using mysql 5.0.15 my query gets a result of "Unknown column 'i.rid' in
> 'on clause'"
>
> There is actually a complete query, but this is a snippet, as the
> selects with subqueries I don't believe will cause the problem. When I
> changed c.rid=i.rid to c.rid=c.rid, the query executes. It is incorrect,
> but at least it runs, so, it appears that that the last join is where
> the problem is.
>
> Any suggestions as to what might be the cause? Thanx.
>
> FROM items i, nams.netids n
> INNER JOIN nams.names AS na ON n.badge=na.badge
> INNER JOIN nams.affiliations AS a ON a.badge=na.badge
> INNER JOIN nams.roles AS r ON a.role=r.code
> LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge)
> LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid)
> WHERE i.rid=12415 AND n.netid='alb' AND i.status='A' AND c.badge IS NULL
> LIMIT 1;
>
<snip>
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black [EMAIL PROTECTED]
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFDgdwkikQgpVn8xrARAky9AJ4lcBoVIsqkU2nBpca6fAZZsl5dvgCfR3H5
> 7ed5A7RcOLrcm2XxQh/zSD8=
> =+Xos
> -----END PGP SIGNATURE-----
>
The query engine became more ANSI compliant with v5.0.12. Beginning with
that version the prioritization of your comma-declared CROSS JOIN ("FROM
items i, nams.netids n") changed so that the explicit JOINS happen first.
That means that when you say "LEFT OUTER JOIN curuse AS c ON
(c.rid=i.rid)" the table `items` has not been added to the "list of tables
participating in this query" yet so it's columns cannot be used as part of
an ON clause.
Two ways around it:
1) use parentheses to modify the JOIN priorities so that the CROSS JOIN
happens first
FROM (items i, nams.netids n)
INNER JOIN nams.names AS na ON n.badge=na.badge
INNER JOIN nams.affiliations AS a ON a.badge=na.badge
INNER JOIN nams.roles AS r ON a.role=r.code
2) use an explicit INNER JOIN or CROSS JOIN command (** my
recommendation).
FROM items i
CROSS JOIN nams.netids n
INNER JOIN nams.names AS na ON n.badge=na.badge
INNER JOIN nams.affiliations AS a ON a.badge=na.badge
INNER JOIN nams.roles AS r ON a.role=r.code
A detailed explanation of this behavior is in THE FINE MANUAL:
http://dev.mysql.com/doc/refman/5.0/en/join.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
