SQL query doesn't return what I expect

Question

The nested query only show a single row. When I add more data, it throws an error.

I'm using SQL Server 2014.

SELECT  
    ListaMaestra.id_ListaMaestra, ListaMaestra.Clave,
    ListaMaestra.Nombre_P, ListaMaestra.Modulo_P,
    ListaMaestra.Caracteristicas, ListaMaestra.Tipo_Formato,
    ListaMaestra.Fecha_Emision, ListaMaestra.Fecha_Revision,
    ListaMaestra.Revision, ListaMaestra.Norma,
    empleado.nombre, cargo.nombre_cargo,
    (SELECT empleado.nombre FROM ListaMaestra, empleado 
     WHERE ListaMaestra.Nombre_Reviso = empleado.id_empleado) AS Nombre_Elaboro,
    (SELECT cargo.nombre_cargo FROM ListaMaestra, cargo  
     WHERE ListaMaestra.Cargo_Reviso = cargo.id_cargo) AS Cargo_Elaboro,
    ListaMaestra.Estatus, ListaMaestra.Ruta_PDF
FROM 
    ListaMaestra, empleado, cargo
WHERE 
    ListaMaestra.Nombre_Elaboro = empleado.id_empleado 
    AND ListaMaestra.Cargo_Elaboro = cargo.id_cargo

ERROR:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

It's unclear exactly what you're trying to select, but the problem should go away if you remove `ListaMaestra` from the `from` clause of each subselect, since you've already referenced `ListaMaestra` in the `from` clause of the main select. If you can clarify exactly what you're trying to select, I can post this as an answer. — Dawood ibn Kareem, Oct 08 '19 at 20:28
Thanks your solution helped me a lot and the problem was solved — Anibal Melchor, Oct 08 '19 at 20:37
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Oct 09 '19 at 04:03
@AnibalMelchor - If one of the answers for [this question of yours](https://stackoverflow.com/questions/58939217/i-am-validating-if-a-resultset-is-empty) resolved your issue, you can help the community by marking it as accepted. An accepted answer helps future visitors use the solution confidently. — Arvind Kumar Avinash, May 29 '20 at 19:06

score 0 · Accepted Answer · answered Oct 09 '19 at 04:42

0

The problem should go away if you remove ListaMaestra from the from clause of each subselect, since you've already referenced ListaMaestra in the from clause of the main select.

answered Oct 09 '19 at 04:42

Programnik

1,449
1
9
13

SQL query doesn't return what I expect

1 Answers1