I have a problem with count in a right join, with this code i can count how many visites have a video
SELECT video_id, COUNT(video_id) AS Views FROM fm_views GROUP BY video_id ORDER BY Views DESC;
SELECT fm_video.*, IFNULL(COUNT(fm_views.video_id), 0) AS Views FROM fm_views RIGHT JOIN fm_video ON fm_video.video_id = fm_views.video_id GROUP BY fm_video.video_id ORDER BY Views DESC, id DESC;
3845 >>4<< This is the problem
right join quite hard to follow.
left join seems more natural because it keeps all rows in the first table. Also,
COUNT() cannot return
NULL. So, this is the query:
SELECT vd.*, COUNT(vw.video_id) AS Views FROM fm_video vd LEFT JOIN fm_views vw ON vd.video_id = vw.video_id GROUP BY vd.video_id ORDER BY Views DESC, id DESC;
If this is returning unexpected results, then it suggests that you have bad data, or your expectation is wrong.
For the first possibility, I would suggest validating that
fm_video(video_id) is the primary key on the table (or at least unique). You can also run this code to see if there are duplicates:
select video_id from fm_video group by video_id having count(*) > 1;
You should also check that
video_id has the same type in both tables.