tags:

views:

52

answers:

3

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?

+3  A: 
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;
ar
I'm not sure how I need to store the results.I use mysqli $stmt->bind_result($var); to fetch results, but with this SQL query I'm not sure how to fetch it.. Any help?
Jonathan
A: 

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!

Brendan Bullen
Also, instead of the if, why not just `COALESCE(posters.posters_count, 0)`? There's a built in function to counter `NULL` values, so why not use it...
ircmaxell
I wasn't aware of this function. Very nice. :)
Brendan Bullen
A: 
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.

vulkanino
I'm not sure how I need to store the results. I use mysqli $stmt->bind_result($var); to fetch results, but with this SQL query I'm not sure how to fetch it.. Any help?
Jonathan
I don't think this works. It will give NULLs instead of 0s.
Artefacto
@Artefacto, are you quite sure about that? I've just tried a similar thing and always get zero, which is what I'd expect from a `COUNT(*)`.
Brian Hooper
@Brian Well, actually no, but in PostgreSQL `COUNT(*)` is different from count(T.movieid) in that respect -- if there are no rows, COUNT(*) gives 0 and count(T.movieid) gives 1. If you say MySQL behaves differently, I'll take your word for it.
Artefacto