CODEWITHSUNDEEP
javamysqlsqldatabaserelational-database
oe a
oe a

Reputation: 2280

Running complex query

My goal is to do two things:
-Count the number of votes (this table is called votes) for each songID in a particular room.
-Find out if the userID has at least one vote for a song(in a particular room)

The userID and RoomID are passed into the queries. I am not sure how to 'loop' through each songID. Do I run two queries - first get each songID and then run a for loop to get the above information (using Java for this)?

enter image description here

Upvotes: 0

Views: 114

Answers (3)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79875

select SongID, count(*) as voteCount, 
case when exists( 
        select 1 from votes b 
        where b.songID = a.songId and b.RoomID = ? and b.UserID = ? ) 
    then 'Yes' else 'No' end case as didUserVote
from votes a
where a.RoomID = ?
group by SongID

If that doesn't work, try this - the third column will be null if the user didn't vote for the song, and UserID if they did.

select a.SongID, count(*) as voteCount, b.UserID
from votes a left join votes b on a.songID = b.songID and b.RoomID = ? and b.UserID = ?
where a.RoomID = ?
group by a.SongID, b.UserID

Upvotes: 1

danisius
danisius

Reputation: 597

Try this in third coulmn if retunr is NULL there is no vote

select SongID, count(*) as voteCount,(
        select b.userid from votes b 
        where b.songID = a.songId and b.RoomID = 131 and b.UserID = 70)
    from votes a
    where a.RoomID = 131
    group by SongID

Upvotes: 1

charris861
charris861

Reputation: 54

You could do this for the first question

SELECT Count(songID), songID
FROM votes
WHERE RoomID = @roomid
GROUP BY songID

And this one for the second question

SELECT songID
FROM votes
WHERE UserID = @userid
      AND RoomID = @roomid

Upvotes: 2

Related Questions