CODEWITHSUNDEEP
sqlsql-server
user5404433
user5404433

Reputation:

Subquery returned more than 1 value error when using SELECT TOP

I'm receving the following error when executing the code below and not sure why I'm getting it because I'm using a SELECT TOP. I cannot see any output when I execute the query but can anyone see what's wrong with it?

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

;WITH cte AS
(
    SELECT f.FixtureID,
        ht.FinalTeamWeight - at.FinalTeamWeight AS TeamScore
    FROM dbo.Fixture f

...

)


UPDATE f 
SET f.HomeScore = s.HomeScore,
    f.AwayScore = s.AwayScore
FROM dbo.Fixture f
INNER JOIN 
(
    SELECT FixtureID,
    TeamScore,
    (
        SELECT
        CASE 
            WHEN c.TeamScore BETWEEN HomeWeighting AND AwayWeighting AND HomeScore > AwayScore AND s.ScoreDifference = sr.ScoreDifference
                THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE HomeScore > AwayScore AND ScoreDifference = 1 ORDER BY NEWID())     
        END AS ScoreID
        FROM dbo.Score s
        LEFT JOIN ScoreReference sr
        ON s.ScoreDifference = sr.ScoreDifference

    ) AS ScoreID -- end select case

FROM cte c

 ) -- end inner join

 AS ScoreResult
    ON f.FixtureID = ScoreResult.FixtureID
INNER JOIN Score s
    ON ScoreResult.ScoreID = s.ScoreID
INNER JOIN ScoreReference sr
    ON s.ScoreDifference = sr.ScoreDifference

TABLES: SCORE_REFERENCE

enter image description here

SCORE:

enter image description here

Upvotes: 0

Views: 421

Answers (2)

Alexandru Aliu
Alexandru Aliu

Reputation: 474

It's probably not the onw with TOP but the one which contains it. Add a TOP 1 on that one also

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

Just add TOP(1) here...

SELECT TOP(1)
        CASE 
            WHEN c.TeamScore BETWEEN HomeWeighting AND AwayWeighting AND HomeScore > AwayScore AND s.ScoreDifference = sr.ScoreDifference
                THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE HomeScore > AwayScore AND ScoreDifference = 1 ORDER BY NEWID())     
        END AS ScoreID
        FROM dbo.Score s
        LEFT JOIN ScoreReference sr
        ON s.ScoreDifference = sr.ScoreDifference

Upvotes: 1

Related Questions