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

Question

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!

Answer 1

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.

Answer 2

Use every where IN clause, so you don't have to bother if you have more than one element

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