Search code examples
mysqlsqlselectcorrelated-subquery

subquery returns more than one row error

Okay, there are.. a number.. of questions on SO with the same title, but I couldn't think of how to be more specific.

The reason that this is confusing me is that returning more than one row is a desired effect of (one of) my sub queries

SELECT MAX(theCount) FROM
    (SELECT FK_Hour, Count(FK_Hour) As theCount FROM 
        (Select FK_Hour 
        From slottime 
        INNER JOIN time ON slottime.FK_Hour = time.Hour 
        WHERE FK_Hour = 
            (SELECT time.Hour FROM time WHERE time.day=0 )
        ) As C 
        GROUP By FK_Hour
    ) AS counts;

I realise that time is a reserved word for MySQL - it is probably something I will have to rectify soon; though I doubt its the problem in this case.

The aim of the query is to select the maximum count of instances of particular hours where day = 0.

MySQL phpmyadmin designer

Solution

  • The problem is clearly:

        WHERE FK_Hour = (SELECT time.Hour FROM time WHERE time.day=0 )

    The simplest solution is to change = to in:

        WHERE FK_Hour IN (SELECT time.Hour FROM time WHERE time.day=0 )

    However, you might want to check your data to determine if this is what you really intend.