bcoffin Posted October 19, 2006 Share Posted October 19, 2006 I've used MySQL 4 for years but don't understand how to translate this queryfor MySQL 5. I've done a cursory search through this forum, but don't see anyqueries that look similar:[code]SELECT t1.*, t2.*,CONCAT(t3.a,'|',t3.b,'|',t3.c) AS abcFROM t1,t2LEFT JOIN t4 ON t1.id = t4.t1_idLEFT JOIN t3 ON t1.id = t3.t1_id AND t3.[some field] = [some value]WHERE t2.t1_id = t1.id AND t2.t1_id = [some id]GROUP BY t1.idORDER BY t1.field1, t1.field2, t1.field3[/code]I'm getting ERROR 1054: (42S22): Unknown column 't3.a' in 'field list'Can someone please help?benny Quote Link to comment Share on other sites More sharing options...
shoz Posted October 19, 2006 Share Posted October 19, 2006 [quote author=bcoffin link=topic=112037.msg454535#msg454535 date=1161279225]I've used MySQL 4 for years but don't understand how to translate this queryfor MySQL 5. I've done a cursory search through this forum, but don't see anyqueries that look similar:[code]SELECT t1.*, t2.*,CONCAT(t3.a,'|',t3.b,'|',t3.c) AS abcFROM t1,t2LEFT JOIN t4 ON t1.id = t4.t1_idLEFT JOIN t3 ON t1.id = t3.t1_id AND t3.[some field] = [some value]WHERE t2.t1_id = t1.id AND t2.t1_id = [some id]GROUP BY t1.idORDER BY t1.field1, t1.field2, t1.field3[/code]I'm getting ERROR 1054: (42S22): Unknown column 't3.a' in 'field list'Can someone please help?benny[/quote]You shouldn't need to change the syntax of the query when using MYSQL 5.If this is not a direct copy and paste of the query that you're using then keep in mind that if you surround t3.a with backticks(`t3.a`) then "t3.a" is seen as a column name rather than a "table.column" reference. Quote Link to comment Share on other sites More sharing options...
shoz Posted October 19, 2006 Share Posted October 19, 2006 After reviewing MYSQL 5 Docs I do see a comment on an error that may occur when mixing "comma joins with other join types"[quote=mysql.com]If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.[/quote]The error is not the same as the one posted, but if it's not a problem now it'll probably come up later on. You can change the query to the following to avoid that.[code]SELECT t1.*, t2.*,CONCAT(t3.a,'|',t3.b,'|',t3.c) AS abcFROM t1INNER JOIN t2 ON t2.t1_id = t1.idLEFT JOIN t4 ON t1.id = t4.t1_idLEFT JOIN t3 ON t1.id = t3.t1_id AND t3.[some field] = [some value]WHERE t2.t1_id = [some id]GROUP BY t1.idORDER BY t1.field1, t1.field2, t1.field3[/code] Quote Link to comment Share on other sites More sharing options...
bcoffin Posted October 19, 2006 Author Share Posted October 19, 2006 I've never used an "inner join" before. I will give that a try right now, Shoz.Hopefully others who are experiencing this same problem will be enlightened (too) by your advice. Quote Link to comment Share on other sites More sharing options...
bcoffin Posted October 19, 2006 Author Share Posted October 19, 2006 Bummer.. I'm still getting the same error: Unknown column 't3.a' in 'field list'...What a tricky query~~! Quote Link to comment Share on other sites More sharing options...
shoz Posted October 19, 2006 Share Posted October 19, 2006 Ensure that column "a" exists in table "t3" and that you also don't have backticks surrounding "t3.a" (`t3.a`). Quote Link to comment Share on other sites More sharing options...
bcoffin Posted October 19, 2006 Author Share Posted October 19, 2006 You = genius;Me = idiot;I thank you for pointing out the error of my ways. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 19, 2006 Share Posted October 19, 2006 Alternatively, you could have just wrapped t1,t2 in parens (t1,t2) to keep the "old" style of precedence intact. But you should never use the comma operator anyway. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.