CODEWITHSUNDEEP
sqlsubquery
Ch An Chao Sae
Ch An Chao Sae

Reputation: 39

SQL subquery error, returned more than 1 value

I am trying to write a query but I don't know why I am getting an error that says:

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

SELECT CAST(Title AS CHAR(30)) AS 'Title'
     , CAST(Content AS CHAR(70)) AS 'Content'
     , PostedDate AS 'Date'
     , CAST( ISNULL(
        (SELECT Upvote
        FROM Ratings
        WHERE Ratings.FK_PostID = Posts.PostID)
        , 0) AS CHAR(10) ) AS 'Upvotes' 
FROM Posts
;

Upvotes: 1

Views: 29

Answers (2)

CinnamonRii
CinnamonRii

Reputation: 156

I'm assuming you want to get the number of votes per post? Why not just use a join:

SELECT p.Title,  p.Content, p.PostedDate AS 'Date', COUNT(r.Upvote) AS 'Upvotes'
                         FROM Posts p
                         LEFT JOIN Ratings r ON r.FK_PostID = p.PostID
                         GROUP BY p.PostID, p.Title ORDER BY p.PostID ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Presumably, you want to count or sum the values. If so:

SELECT p.Title, p.Content, p.PostedDate AS Date,
       (SELECT SUM(r.Upvote)
        FROM Ratings r
        WHERE r.FK_PostID = p.PostID
       ) as Upvotes
FROM Posts p;

Notes:

  • SUM(r.upvote) may not be the correct logic. Perhaps you want COUNT(*) or something similar.
  • I see no reason to cast the columns to strings.
  • Only use single quotes for string and date constants.

Upvotes: 3

Related Questions