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?