Jump to content


Photo

mySql 5 breaks query


  • Please log in to reply
7 replies to this topic

#1 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 19 October 2006 - 05:33 PM

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:

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

I'm getting ERROR 1054: (42S22): Unknown column 't3.a' in 'field list'

Can someone please help?

benny
Ben Coffin
bcoffin@hotmail.com

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 October 2006 - 06:20 PM

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:

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

I'm getting ERROR 1054: (42S22): Unknown column 't3.a' in 'field list'

Can someone please help?

benny


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.

#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 October 2006 - 06:41 PM

After reviewing MYSQL 5 Docs I do see a comment on an error that may occur when mixing "comma joins with other join types"

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.


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.

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


#4 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 19 October 2006 - 07:24 PM

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.
Ben Coffin
bcoffin@hotmail.com

#5 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 19 October 2006 - 07:28 PM

Bummer.. I'm still getting the same error: Unknown column 't3.a' in 'field list'...
What a tricky query~~!
Ben Coffin
bcoffin@hotmail.com

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 October 2006 - 07:33 PM

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

#7 bcoffin

bcoffin
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts

Posted 19 October 2006 - 07:48 PM

You = genius;
Me = idiot;

I thank you for pointing out the error of my ways.
Ben Coffin
bcoffin@hotmail.com

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 10:09 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users