MySql count 3 tables with left join

Issue

Given the following 3 tables of video website database:

video

id    title
10    video1

view (table for counting views of videos)

id     userIP      videoId
1      1.1.1.1     10
2      2.2.2.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 V.id, count(VW.id) as views, count(L.id) as likes
FROM video V 
     LEFT JOIN view VW
ON VW.videoId = V.id
     LEFT JOIN `like` L
ON L.videoId = V.id
WHERE V.id=10
GROUP BY V.id, VW.id, L.id

Solution

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

SELECT 
  (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