Jan Pieter Kunst [EMAIL PROTECTED] wrote on 02/21/2006 04:54:46 AM:
On 2/20/06, Eric Persson [EMAIL PROTECTED] wrote:
Hi,
I have a query which works fine for me in my 4.1 environment, but when
moved to the 5.0.18 environment, it fails with the result below:
mysql SELECT r.uid, u.username, u.image_type, count(id) AS antal,
s.timestamp FROM recruits_uid r, users u, users u2 LEFT JOIN sessions
s
ON s.uid=u.uid WHERE r.uid=u.uid AND r.rec_uid=u2.uid AND
u2.deleted=0
AND datetime-00-00 00:00:00 GROUP BY r.uid ORDER BY antal DESC
LIMIT 100;
ERROR 1054 (42S22): Unknown column 'u.uid' in 'on clause'
mysql
It seems like the table alias u is not recognized for some reason.
Does
anyone have any hints about this?
Yes, try this:
LEFT JOIN sessions s ON (s.uid=u.uid)
the ON-clause enclosed in parentheses.
JP
JP,
That's not what Gabriel meant when he said to use parentheses
The original table ref portion of the original query contained several
tables that were implicitly cross joined by commas. Eric will need to use
parentheses in order to change the order of operations (the join sequence)
to include one of his comma-listed tables first or he will need to rewrite
his query to use the explicit JOIN ON syntax for all of the table def
components of his table ref clause.
The demotion of the comma operator as of 5.0.12 makes MySQL more compliant
with the applicable SQL standards. Unfortunately there were many users who
blindly followed the examples in the documentation and only learned to use
commas when they wanted to declare INNER JOINs. Or, they come from
Oracle-like systems who use only commas to declare INNER JOINs (and the
outer ones, too).
Full details on the change and it's impact on query design can be found
here:
http://dev.mysql.com/doc/refman/5.0/en/join.html
I have requested that the documentation be rewritten so that the explicit
forms of JOIN declarations are used more often than the implicit form. I
do not know the status of making those changes. Perhaps if other could
make their recommendations, fewer of our new users will run into this same
issue.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
