CODEWITHSUNDEEP
mysqlsql
Leo Gutierrez
Leo Gutierrez

Reputation: 21

I have error 1242 subquery return more than 1 row

select (select Nombre 
        from Pacientes 
        where idPacientes= any(select idPaciente 
                                from Citas 
                                where Dias_idDia= any(select idDia 
                                                      from Dias 
                                                      where fecha = '2013-10-15'))) as 'Nombre',horaInicio, horaTermino,actividad,observacion,recordar,ciudad,tipoCita 
from Citas 
where Dias_idDia = any (select idDia 
                        from Dias 
                        where fecha='2013-10-15')
order by horaInicio;

I have error 1242, if somebody could help to fix this because it is giving me so much trouble in my system. ty

Upvotes: 2

Views: 1178

Answers (2)

Dev
Dev

Reputation: 3580

   select (select Nombre 
       from Pacientes 
       where idPacientes in (select idPaciente 
                            from Citas 
                            where Dias_idDia in (select idDia 
                                                  from Dias 
                                                   where fecha = '2013-10-15'))) as
  'Nombre',horaInicio, horaTermino,actividad,observacion,recordar,ciudad,tipoCita 
   from Citas 
   where Dias_idDia in (select idDia 
                    from Dias 
                    where fecha='2013-10-15')
   order by horaInicio;

Upvotes: 2

andy
andy

Reputation: 6079

Incorrect number of rows from subquery:

ERROR 1242 (ER_SUBSELECT_NO_1_ROW) SQLSTATE = 21000 Message = "Subquery returns more than 1 row" This error occurs for statements where the subquery must return at most one row but returns multiple rows. Consider the following example:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

If SELECT column1 FROM t2 returns just one row, the previous query will work. If the subquery returns more than one row, error 1242 will occur. In that case, the query should be rewritten as:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

Reference

Upvotes: 4

Related Questions