MySQL: OUTER JOIN and COUNT()

Question

I have table 'posters', table 'reviews' and table 'trailers'. Every table is connected with an movieID column, but each table can be empty for certain movieIDs:

 ++ posters_table +++      ++ reviews_table ++      ++ trailers_table ++

--itemID--+--filename-    --itemID--+--review--    --itemID--+--trailer
----------------------    ---------------------    ---------------------
----001---+--0012343--    ----004---+--blalba--    ----002---+--002345--
----001---+--0013331--    ----004---+--xlalxa--    ----005---+--005434--
----002---+--0020052--    ----005---+--zlalza--    ----001---+--005335--

I want to COUNT() the number of posters, reviews and trailers for the specified movieID and get 0 if no available.

So if I want to count movieID = 001 I get: ['posters'] = 2 / ['reviews'] = 0 and ['trailers'] = 1 (for example)

Can someone post the SQL query to do this?

Answer 1

select
    (select count(*) from posters_table  where itemId = ?) as posters,
    (select count(*) from reviews_table  where itemId = ?) as reviews,
    (select count(*) from trailers_table where itemId = ?) as trailers;

Answer 2

I think if you JOIN the movie table to a result set that gives the count, you can then pick out whether or not the count is > 0 and give the appropriate value:

SELECT movieID, IF(posters.posters_count > 0, posters.posters_count,0) AS posters_total,  IF(reviews.reviews_count > 0, reviews.reviews_count,0) AS reviews_total,  IF(trailers.trailers_count > 0, trailers.trailers_count,0) AS trailers_total
FROM movies m
LEFT JOIN (SELECT itemID,COUNT(*) AS posters_count FROM posters_table WHERE itemID = '001' GROUP BY itemID) posters ON posters.itemID = movies.movieID
LEFT JOIN (SELECT itemID,COUNT(*) AS reviews_count FROM reviews_table WHERE itemID = '001' GROUP BY itemID) reviews ON reviews.itemID = movies.movieID
LEFT JOIN (SELECT itemID,COUNT(*) AS trailers_count FROM trailers_table WHERE itemID = '001' GROUP BY itemID) trailers ON trailers.itemID = movies.movieID
WHERE m.movieID = '001'

EDIT: I prefers ar's solution. Much simpler!

Answer 3

select 
    (select count(P.movieid) from posters P where P.movieid=1),
    (select count(R.movieid) from reviews R where R.movieid=1),
    (select count(T.movieid) from trailers T where T.movieid=1)

The tables are not "really" joined so you need three selects.