why this query doesn't work, im trying to compare the field idlic with an existing user that belongs to that idlic.
the returning message from mysql is an unknown column, how do i save the value for used in a comparison.
select *, (select licencia from cuentas where idcuenta = ht.cuenta) as idlic
from hojastrabajo ht
where idlic = (select licencia from asociadolicencia where usuario = (select idusuario from usuarios where nombre = 'desarrollador' ))
You cannot use a column alias in the same select or where that it is defined. MySQL has a convenient work-around, where you can use a having clause instead:
select ht.*, (select licencia from cuentas where idcuenta = ht.cuenta) as idlic
from hojastrabajo ht
having idlic = (select licencia
from asociadolicencia
where usuario = (select idusuario from usuarios where nombre = 'desarrollador' )
);
(select licencia from cuentas where idcuenta = ht.cuenta) as idlic be considered as bad practice, or is this perfectly normal and is just a preference?
left join in this case. There are some situations where a correlated subquery has advantages, but this is not one of them.Could you try this?
select ht.*, c.licencia
from hojastrabajo ht
left join cuentas c
on c.idcuenta = ht.cuenta
where c.licencia =
(select licencia from asociadolicencia where usuario =
(select idusuario from usuarios where nombre = 'desarrollador' )
)
you can't use what you bring in "as idlic" in where statement. having a query as one of the select field is also not a good idea what if the (select licencia ...) returns more than one result. you need to join these tables and the put the whole conditions in where statement.
