Here is my table layout: What I would like to do: Count the number of votes for each songID in a particular room (passed in as a parameter). Find out if the userID has at least one vote for a song in a particular room. UserID is also passed in as a parameter. An extension of the two above. If

Complicated MySQL Select query

Here is my table layout:

Screenshot of database layout

What I would like to do:

Count the number of votes for each songID in a particular room (passed in as a parameter).
Find out if the userID has at least one vote for a song in a particular room. UserID is also passed in as a parameter.
An extension of the two above. If there are no votes for a song, I would like to return 0 for those entries.

What I have:

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

(feel free to use those RoomID and UserID numbers in examples)

The above SQL statement does 1 and 2 but not 3. Is there any way to extend it to do 3? It would be nice to get all this done in a single query. Right now it returns all songs that have a vote, the number of votes and if the UserID in question has voted (null otherwise). Thanks in advance.

Just to clarify a bit, currently, this statement will only output songs with entries in the votes table, I’m trying to modify it to include songs that do not have entries in the votes table (that is, to include all songs in Select SongID from songs where RoomID = 178). Also keeping in mind everything is room-specific. Songs are matched to a certain room by their SongID.

Here is my song table for example:

+--------+--------+
| SongID | RoomID |
+--------+--------+
|   2835 |    178 |
|   2836 |    178 |
|   2837 |    178 |
|   2838 |    178 |
|   2839 |    178 |
|   2840 |    178 |
|   2841 |    178 |
|   2842 |    178 |
|   2843 |    178 |
|   2844 |    178 |
|   2845 |    178 |
|   2846 |    178 |
|   2847 |    178 |
|   2848 |    178 |
+--------+--------+

Here is the votes:

+--------+--------+--------+
| SongID | UserID | RoomID |
+--------+--------+--------+
|   2835 |     71 |    178 |
|   2836 |     71 |    178 |
|   2837 |     71 |    178 |
|   2838 |     71 |    178 |
|   2839 |     71 |    178 |
|   2840 |     71 |    178 |
|   2841 |     71 |    178 |
|   2842 |     71 |    178 |
+--------+--------+--------+

And finally, the result of the query I have so far:
Only the first 7 songs have votes and only those are returned. I would like to modify this query to include ALL songs that are in the room, even if they have no votes (and return the vote count as 0 or null).

+--------+-----------+--------+
| SongID | voteCount | UserID |
+--------+-----------+--------+
|   2835 |         1 |     71 |
|   2836 |         1 |     71 |
|   2837 |         1 |     71 |
|   2838 |         1 |     71 |
|   2839 |         1 |     71 |
|   2840 |         1 |     71 |
|   2841 |         1 |     71 |
+--------+-----------+--------+

Answer

I believe your query might look like this

SELECT s.SongID, 
       COALESCE(v.allVotes, 0) allVotes, 
       COALESCE(v.userVotes, 0) userVotes
  FROM songs s LEFT JOIN 
(
  SELECT SongId, 
         COUNT(*) allVotes,
         SUM(CASE WHEN UserID = 71 THEN 1 ELSE 0 END) userVotes
    FROM votes
   WHERE RoomID = 178 
   GROUP BY SongID
) v 
    ON s.songID = v.songID 
 WHERE s.RoomID = 178

Here is SQLFiddle demo

Advertisement

Answer