Unknown column error in MySQL (#1054)

Question

This sql fails:

select * from RRICallouts as r 
    JOIN LevelToCalloutsJT as lc on ( `r.__kp_RecID` = `lc._kf_RecID~Callout` ) 
    JOIN Levels as l ON ( `lc._kf_RecID~Level` = `l.__kp_RecID` ) 
  where `l.__kp_RecID` = 201006221644060009

#1054 - Unknown column 'l.__kp_RecID' in 'where clause

This works:

select `__kp_RecID` from Levels as l ;

Using MySQL 5.0.77 on some linux variant

have you tried deleting different parts of the query and then re-testing it to isolate the problem? ie try it with out the WHERE statement, then try it without the second JOIN etc.. — Haroldo
– Haroldo, Commented Aug 5, 2010 at 16:07
Does the equivilent SQL query here also fail? select * from RRICallouts as r, Levels as l JOIN LevelToCalloutsJT as lc on ( r.__kp_RecID = lc._kf_RecID~Callout ) where l.__kp_RecID = 201006221644060009 AND lc._kf_RecID~Level = l.__kp_RecID — bot403
– bot403, Commented Aug 5, 2010 at 16:08
I do not envy you having to work on a database with such memorable column names. Is this a Hungarian goulash? — Brian Hooper
– Brian Hooper, Commented Aug 5, 2010 at 16:14
Welcome to SO. +1 for a well-written question: Here's what I'm doing; Here's what I tried; Here's what happened that I don't understand. Meets all the guidelines :-) — Jim Garrison
– Jim Garrison, Commented Aug 5, 2010 at 16:17

Daniel Vassallo · Accepted Answer · 2010-08-05 16:08:31Z

3

The problem is in your backticks. You should use them as follows:

`r`.`__kp_RecID`

... instead of:

`r.__kp_RecID`

Test case:

CREATE TABLE test (id int, value int);
INSERT INTO test VALUES (1, 100);

SELECT `t`.`id` FROM test AS t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SELECT `t.id` FROM test AS t;
ERROR 1054 (42S22): Unknown column 't.id' in 'field list'

answered Aug 5, 2010 at 16:08

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

andyknas Over a year ago

backticks was the problem. many thanks. the column names are truly messy, but the db name (provided by the host) is even better - 481986_G5ikr6eJy9wmxRm0

Brian Hooper Over a year ago

I don't envy you that, either!

Collectives™ on Stack Overflow

Unknown column error in MySQL (#1054)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related