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.