mySql 5 breaks query

[bcoffin]

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

[shoz]

[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.

[shoz]

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]

[bcoffin]

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.

[bcoffin]

Bummer.. I'm still getting the same error: Unknown column 't3.a' in 'field list'...
What a tricky query~~!

[shoz]

Ensure that column "a" exists in table "t3" and that you also don't have backticks surrounding "t3.a" (`t3.a`).

[bcoffin]

You = genius;
Me = idiot;

I thank you for pointing out the error of my ways.

[fenway]

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.