MySql count 3 tables with left join


Given the following 3 tables of video website database:


id    title
10    video1

view (table for counting views of videos)

id     userIP      videoId
1     10
2     10

like (table for counting likes in videos)

id     userID    videoId
1      1001      10

I want to write MYSQL query to count views and likes for each video, so in that example i should receive:

videoId    views     likes
10         2         1

I tried to run the following query, but it displayed wrong output:

SELECT, count( as views, count( as likes
FROM video V 
     LEFT JOIN view VW
ON VW.videoId =
     LEFT JOIN `like` L
ON L.videoId =


This will work, see that I only count once per query

  (SELECT COUNT(videoId) FROM view WHERE videoId=10) as view,
  (SELECT COUNT(videoId) FROM like WHERE videoId=10) as like;

Answered By – Nestor Mancilla

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published