I have 2 tables (movies and watch).
I want to know which movies is being watched by how many people.
I did this:
select movieID, count(persID) from watch group by persID;
which gives me basically what i want. The only problem is that movies that aren't being watched by anyone won't show up in my result table as 0 viewers but they are just left out.
I want to achieve this in two different ways. Using UNION and the other way using a subquery.
So you got the list of movies that are being watched and the counter - left join it into the list of all movies:
SELECT * FROM Movies m LEFT JOIN (select movieID, count(persID) as countwatch from watch group by persID) w ON m.movieid = w.movieid
Left join means you get all the movies, linked up with counts of only those being watched. If a movie is not being watched it's countwatch column will be null
If you want to turn that null into a 0, use COALESCE:
SELECT m.*, COALESCE(w.countwatch, 0) as countwatch FROM Movies m LEFT JOIN (select movieID, count(persID) as countwatch from watch group by persID) w ON m.movieid = w.movieid