CODEWITHSUNDEEP
mysql
Dennis Weber
Dennis Weber

Reputation: 11

MYSQL ERROR 1242: Subquery returns more then 1 row

i got a prob. I want to get all Users, where another user has to play against next. My sql select is looking like this:

SELECT Benutzer.benutzername
                  FROM Benutzer
                  WHERE Benutzer_ID =
                  (SELECT Benutzer_ID_1
                  FROM Spiel WHERE NextToPlay ='35'
                  AND Benutzer_ID_2 ='35' )
                  AND
                  Benutzer_ID =
                  (SELECT Benutzer_ID_2
                  FROM Spiel WHERE NextToPlay ='35'
                  AND Benutzer_ID_1 ='35');

But always got the error, that my subquery returns more then 1 row. Could someone help me?

Upvotes: 1

Views: 49

Answers (2)

KingGeekus
KingGeekus

Reputation: 36

One or both of your subqueries is returning more than one result. As you are using the = operator you are required to return a single result. You could instead run using an IN operator to match more than one result:

SELECT Benutzer.benutzername
              FROM Benutzer
              WHERE Benutzer_ID IN
              (SELECT Benutzer_ID_1
              FROM Spiel WHERE NextToPlay ='35'
              AND Benutzer_ID_2 ='35' )
              AND
              Benutzer_ID IN
              (SELECT Benutzer_ID_2
              FROM Spiel WHERE NextToPlay ='35'
              AND Benutzer_ID_1 ='35');

Upvotes: 0

exussum
exussum

Reputation: 18550

SELECT Benutzer.benutzername
                  FROM Benutzer
                  WHERE Benutzer_ID IN
                  (SELECT Benutzer_ID_1
                  FROM Spiel WHERE NextToPlay ='35'
                  AND Benutzer_ID_2 ='35' )
                  AND
                  Benutzer_ID IN
                  (SELECT Benutzer_ID_2
                  FROM Spiel WHERE NextToPlay ='35'
                  AND Benutzer_ID_1 ='35');

When using sub queries you should look at using IN which allows more than 1 result.

Unless you are certain your query will only return 1 row

Upvotes: 1

Related Questions