[SOLVED] Replace comparison to scalar subquery by inner join or left/right join

Replace comparison to scalar subquery by inner join or left/right join

I need to write this query using inner joins or right/left joins but I don't know how to start:

select * from radicados where asignado = 
    (select estudianteid from estudiantes where usuario =
        (select usuarioid from usuarios where nombre =  $nombre_usuario))

But I don't know how to do the same with joins.

I think this must be something like:

select * from radicados inner join usuarios on usuarioid=usuario

Solution

It appears you want something like this:

select radicados.*
from
  radicados
  join estudiantes
    on radicados.asignado = estudiantes.estudianteid
  join usarios
    on estudiantes.usario = usarios.usarioid
  where usarios.nombre = $nombre_usuario

In constructing such a query, start with the FROM clause. Join together the various tables containing the needed data, based on the relationships between them. If needed, add a WHERE clause describing any additional conditions on which you want to filter the result of your join. Then fill in the SELECT list as appropriate.

Under some circumstances you may need to add other clauses, too (ORDER BY, GROUP BY, etc.), but that's not bad once you understand basic queries.