"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>"0000-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
