Jump to content

Archived

This topic is now archived and is closed to further replies.

bcoffin

mySql 5 breaks query

Recommended Posts

I've used MySQL 4 for years but don't understand how to translate this query
for MySQL 5. I've done a cursory search through this forum, but don't see any
queries that look similar:

[code]
SELECT t1.*, t2.*,
CONCAT(t3.a,'|',t3.b,'|',t3.c) AS abc
FROM t1,t2
LEFT JOIN t4 ON t1.id = t4.t1_id
LEFT 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.id
ORDER 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

Share this post


Link to post
Share on other sites
[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 query
for MySQL 5. I've done a cursory search through this forum, but don't see any
queries that look similar:

[code]
SELECT t1.*, t2.*,
CONCAT(t3.a,'|',t3.b,'|',t3.c) AS abc
FROM t1,t2
LEFT JOIN t4 ON t1.id = t4.t1_id
LEFT 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.id
ORDER 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.

Share this post


Link to post
Share on other sites
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 abc
FROM t1
INNER JOIN t2 ON t2.t1_id = t1.id
LEFT JOIN t4 ON t1.id = t4.t1_id
LEFT JOIN t3 ON t1.id = t3.t1_id AND t3.[some field] = [some value]
WHERE t2.t1_id = [some id]
GROUP BY t1.id
ORDER BY t1.field1, t1.field2, t1.field3
[/code]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Bummer.. I'm still getting the same error: Unknown column 't3.a' in 'field list'...
What a tricky query~~!

Share this post


Link to post
Share on other sites
Ensure that column "a" exists in table "t3" and that you also don't have backticks surrounding "t3.a" (`t3.a`).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.