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

Question

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

score 4 · Accepted Answer · answered Jan 28 '15 at 21:53

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.

+1 for "constructing a query" stuff. @LuisContreras: The original query can return runtime errors when the ones in the answers do not. See my (corrected) answer. — philipxy, Jan 28 '15 at 23:23

score 1 · Answer 2 · answered Jan 28 '15 at 21:54

This is the same as

SELECT estudiantes.* FROM radicados 
JOIN estudiantes ON estudianteid = asignado
JOIN usuarioid ON usuarioid = usuario 
WHERE nombre =  $nombre_usuario

I assume here that column names are unique. If not you have to add table names to field names.

philipxy · Answer 3 · 2015-01-30T03:11:15.270

Don't think in terms of picking individual values from tables. Think in terms of rows that meet criteria. Even better think of a table as holding rows that make a corresponding statement true and a query as calculating rows that make a corresponding statement true. First you pick the statement; then you write the query that corresponds to that statement. Here is how to design queries.

//*
all radicados columns of the rows where
    a subrow is IN radicados AND a subrow is IN estudiantes
AND asignado = estudianteid
AND a subrow is IN usarios
AND usuarioid = usuario
AND nombre = $nombre_usuario
*//

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

When your original query reports no runtime errors then this query returns the same rows. But it is not necessarily an equivalent query because your original query reports a runtime error when there is more than one user id (usarios usuarioid) for the given user number (usuarios nombre) or more than one student id (estudiantes estudianteid) for the chosen user id (usuarios usario).

//*
all columns of the radicados rows where
asignado =
    the one estudianteid shared by the estudiantes rows where
    usuario =
       the one usuarioid shared by the usuarios rows where
       nombre = $nombre_usuario
*//

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

This is because expression value = (SELECT ...) is runtime-error-free only if the SELECT returns a table with one column and one row. If so it compares value to the one value in that table but otherwise it gives an error.

If you gave your table definitions and if you declared certain candidate key (PRIMARY KEY or UNIQUE NOT NULL) or FOREIGN KEY constraints then we might know that there will be no errors. When you know that there will be no errors then you should declare the corresponding constraints.

Although probably this new query is the one that you really wanted anyway.

i know how to make queries like that but i dont know the difference in using joins or sub queries. — Luis Contreras, Jan 30 '15 at 01:08
1. I can't tell what you are trying to say by your comment. Please explain using more words. 2. Why did you write this (unclear) fact? What is your point? Are you trying to ask a question? PS: Did you read the link? PPS: *table* `ON/WHERE` *value* `=` *subquery* is *table* rows with *value* equal to the single value in *subquery*; but *table* `JOIN` *subquery* `ON/WHERE` *test* is rows made from a combination of a row from *table* AND a row from *subquery* AND that pass the *test*. Decide what you want to say then translate to SQL. — philipxy, Jan 30 '15 at 01:27

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

3 Answers3

Linked