CODEWITHSUNDEEP
sql-server
Scott Hinshaw
Scott Hinshaw

Reputation: 23

Difficult Sql query for a forum like application

I am creating a web app that has some similar functionality as stackoverflow. Users can create a post and vote on posts. These posts I call Ideas

I am trying to get a query that returns all posts and also returns a 1 or 0 if the signed in user has a vote on a particular post.

Here is what I have so far:

SELECT idea.[IdeaID], 
    Idea.Title, 
    idea.Description,
    idea.Status,
    idea.DateCreated, 
    count(votes.IdeaID) as ideaVoteCount
FROM Idea   
FULL OUTER JOIN Votes 
    on (idea.IdeaID = Votes.IdeaID)
GROUP BY idea.IdeaID,
    Idea.Title, 
    idea.Description, 
    idea.Status,
    idea.DateCreated

This is working correctly so far. It is returning all idea posts and a new column with the count each idea has. I now want a column next to it that displays a 1 or 0 for each idea if the user has voted on it based on a userid.

Any help would be much appreciated!!

EDIT: I have a junction? table I am using to store votes. Votes contains an ideaid and a userid. UserID is coming from a table of user information.

Upvotes: 1

Views: 59

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

try this..

SELECT I.[IdeaID], 
    I.Title, 
    I.Description,
    I.Status,
    I.DateCreated, 
    sum(case when votes.IdeaID is null then 0 else 1 end) as ideaVoteCount,
     case when exists(Select 1 from Votes where userid=@userid and ideaid=I.ideaid) then 1 else 0 end as voteexists
FROM Idea I
LEFT JOIN Votes 
    on (I.IdeaID = Votes.IdeaID)
GROUP BY I.IdeaID,
    I.Title, 
    I.Description, 
    I.Status,
    I.DateCreated,
    votes.ideaid

Upvotes: 1

Related Questions