Mysql – SQL Error [1242] [21000]: Subquery returns more than 1 row (AMATEUR)

dbeaverMySQL

Basically, everything works in the brackets by itself. The statement in the brackets bring back 4 different values all on different rows.

How can I get around the fact that I need to apply the sql statement outside the brackets to the 4 values returned from the statements in the brackets when subqueries can only return 1 row?

SELECT 
    to2.Name, to2.PhoneNum
FROM
    tbl_operatordesc to2
WHERE
    to2.operatorID = (SELECT 
            to3.operatorID
        FROM
            tbl_operatorrouterelation to3
        WHERE
            to3.routeID = (SELECT 
                    tr.routeID
                FROM
                    tbl_route tr
                WHERE
                    tr.`Start` = (SELECT 
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')
                        OR tr.Destination = (SELECT 
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')))

Thanks!

Best Answer

You should be joining between each table on the fields you're trying to compare in the WHERE clause. Then your statement can be on top of that.

Related Solutions

Mysql – HELP with SQL WHERE syntax

As Martin Smith and dezso commented, you can add parentheses to your query to be explicit in your intent since they can change how the where clause is evaluated:

SELECT DISTINCT ID AS IDs
FROM DBTABLE
WHERE ((ID = 1423) AND (Tags LIKE '%Warm%'))
OR ((ID = 2743) AND (Tags LIKE '%Dry%'))
OR ((ID = 4832) AND (Tags LIKE '%Cool%'))

This returns a 3-record result set containing 1423, 2743 & 4832 from your example data.

Parentheses can also make your SQL queries more readable and maintainable.

MySQL – Subquery Returns More Than One Row Error

You need to add the LIMIT clause

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;

This is an oversimplified answer.

If there is a row with PK=1, you find it.

If not, a full table scan may ensue because of gameid not being indexed.

You are better off splitting the query with a UNION. Perhaps, like this:

SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

Since I do not know the data, I cannot predict any performance results.

Give it a Try !!!

UPDATE 2014-01-23 11:01 EST

The reason it works because of the goal I set for the query : Retrieve one value.

First look at the UNION

    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)

The first SELECT can return at most one row.

The second SELECT can return at most one row because of the LIMIT 1.

In the worst case scenario, the UNION will have two rows.

Now look at the whole query:

SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

The outer part of the query will receive either 0, 1, or 2 rows.

Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.

Best Answer

Related Solutions

Mysql – HELP with SQL WHERE syntax

MySQL – Subquery Returns More Than One Row Error

UPDATE 2014-01-23 11:01 EST

Related Question