MySQL - count value returns 1 when should be 0

Viewed 72

The following SQL is from a video-based online courses system. The query brings up a course instance, for a given user, and also brings up info on which videos of the course he or she has or hasn't yet watched.

SELECT
    GROUP_CONCAT(
        COALESCE(watches.num_watches, 0) ORDER BY cvi.position
    ) AS watches_per_vid
FROM course_instances tci
LEFT JOIN videos 
    ON tci.node IN (
       SELECT node_id 
       FROM _taxonomy_assocs 
       WHERE item_id = videos.id
    )
LEFT JOIN course_indexes cvi 
    ON videos.vimeo_id = cvi.vimeo_id 
    && cvi.course_id = tci.node
LEFT JOIN (
    SELECT watches.vid_id, SUM(times) as num_watches
    FROM watches
    GROUP BY watches.vid_id
) watches 
   ON watches.vid_id = videos.id
WHERE tci.user = "vgaz1kjbc65" 
&& node = "jmi_gmoan26" #<-- for example
GROUP BY tci.id

For watches_per_vid, this results in, for example,

4,4,5,2,2,2,1

However I'm finding that even if a user hasn't yet watched a video - which means there is no entry in the watches table for this user and video, it produces 1, not 0.

Here is the watches table:

field           | type         | null   | default            | extra
------------------------------------------------------------------------------------------
id (primary)      varchar(11)    n
user_id           varchar(11)    n
vid_id            varchar(11)    n
latest_stamp      timestamp      n        CURRENT_TIMESTAMP    ON UPDATE CURRENT_TIMESTAMP
times             int(11)        n        1

Can anyone see why this might be happening? I can of course provide more structural code if required.

1 Answers
Related