Reputation: 2657
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 | |
+-------+-------------+------+-----+---------+----------------+
Upvotes: 1
Views: 1174
Reputation: 204934
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
Upvotes: 2
Reputation: 224
Your alias rtu is only available in your subquery, not in the main one.
Upvotes: 0