Error 1242 Mysql subquery

Question

Error 1242 Mysql subquery

Navigation

#1 by (3 votes)
#2 by (0 votes)
#3 by (0 votes)

3

I have a problem with this subquery.

I have in a table two id, cveLider and cveOperador that are related to cveEmpleado , I have to get the name of both, I did it through a subquery but I get the error

ERROR 1242 Subquery returns more than 1 row

Here's my question.

SELECT idSolicitud
    ,(SELECT CONCAT (
                sol.cveLider
                ,'  '
                ,em.nomEmpleado
                ,em.ap1Empleado
                ,' '
                ,em.ap2Empleado
                )
        FROM empleado em
            ,solicitud sol
        WHERE sol.cveLider = em.cveEmpleado) AS l
    ,(SELECT CONCAT (
                s.cveOperador
                ,'  '
                ,emp.nomEmpleado
                ,emp.ap1Empleado
                ,' '
                ,emp.ap2Empleado
                )
        FROM empleado emp
            ,solicitud s
        WHERE s.cveOperador = emp.cveEmpleado) AS o
FROM solicitud
GROUP BY idSolicitud

mysql sql mysqli

asked by Gaby Hdz 02.01.2017 в 16:36

source

3 answers

0

The problem is exactly what it tells you: One of the subqueries returns more than one row. It would be necessary to have a copy of the database to verify, but it is that

select ... from empleado em, solicitud sol where sol.cveLider=em.cveEmpleado

or

select ... from empleado emp, solicitud s where s.cveOperador= emp.cveEmpleado

match more than one row. Test them separately to verify what it is. It is only possible to use subqueries when they return a single row.

However, I would recommend using join , that query looks pretty bad. Please, publish the structure and data of both tables to be able to offer you more details.

answered by 02.01.2017 в 16:49

0

Apparently I just had to make some modifications to my subqueries

    select idSolicitud,
    (select distinct CONCAT(sol.cveLider,'  ', em.nomEmpleado, em.ap1Empleado,' ', em.ap2Empleado)
    from empleado em
    where sol.cveLider=em.cveEmpleado limit 1) as l,
    (select distinct CONCAT(sol.cveOperador,'  ', emp.nomEmpleado, emp.ap1Empleado,' ', emp.ap2Empleado)
    from empleado emp
    where sol.cveOperador= emp.cveEmpleado limit 1) as o
    from solicitud sol
    group by idSolicitud, cveLider, cveOperador

answered by 02.01.2017 в 16:52

Name error: Name "server" not defined Python Problem Sign in of Facebook with Android studio

score 3 · Accepted Answer

Since you are using a subquery to return a cell, you must ensure that only one value is returned or limit it to only returning one row with LIMIT 1 .

Something similar to the following.

select CONCAT(sol.cveLider,'  ', em.nomEmpleado, em.ap1Empleado,' ', em.ap2Empleado)
    from empleado em, solicitud sol
    where sol.cveLider=em.cveEmpleado LIMIT 1

But I recommend that you redefine your query, because if you return many records, this sub-query as you are using it can slow the execution of the query