mysql query error 1054 - unknown column

Question

I failed to run a query in mysql. Deliberately I dont want to do select id from roles...

the query

select rtu.role_id
from roles r 
where id =(select role_id 
           from roles_to_user rtu 
           where user_id=1)

error

ERROR 1054 (42S22): Unknown column 'rtu.role_id' in 'field list'

desc roles_to_user:

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| user_id | int(11) | NO   | PRI | NULL    |       |
| role_id | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+

desc roles:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(80) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

juergen d · Accepted Answer · 2013-07-09 17:06:14Z

2

You can't use columns from a inner select outside it like this. Select r.id instead

select r.id
from roles r 
where id = (select role_id 
            from roles_to_user rtu 
            where user_id = 1)

BTW that will fail if the inner select returns more than 1 record. Use a join instead

select rtu.role_id
from roles r 
inner join roles_to_user rtu on rtu.role_id = r.id
where rtu.user_id = 1

edited Jul 9, 2013 at 17:06

answered Jul 9, 2013 at 16:59

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

2 Comments

Nir Over a year ago

beside syntax, is there a difference between the two?

juergen d Over a year ago

Yes. The 1st query fails for multiple roles for a user. The 2nd returns a record for every row. For many records there could be a performance difference, but that could be examined using explain select ...

Ezra Nugroho · Accepted Answer · 2013-07-09 16:59:47Z

0

Your alias rtu is only available in your subquery, not in the main one.

answered Jul 9, 2013 at 16:59

Ezra Nugroho

2241 silver badge7 bronze badges

Collectives™ on Stack Overflow

mysql query error 1054 - unknown column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related