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?

a'r · Accepted Answer · 2010-08-04 12:46:07Z

3

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;

answered Aug 4, 2010 at 12:46

a'r

37.2k7 gold badges68 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jonagoldman Over a year ago

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?

Brendan Bullen · Accepted Answer · 2010-08-04 12:50:47Z

0

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!

answered Aug 4, 2010 at 12:50

Brendan Bullen

11.8k1 gold badge33 silver badges40 bronze badges

2 Comments

ircmaxell Over a year ago

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...

Brendan Bullen Over a year ago

I wasn't aware of this function. Very nice. :)

vulkanino · Accepted Answer · 2010-08-04 12:56:34Z

0

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.

answered Aug 4, 2010 at 12:56

vulkanino

9,1347 gold badges47 silver badges73 bronze badges

3 Comments

jonagoldman Over a year ago

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?

Brian Hooper Over a year ago

@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(*).

Artefacto Over a year ago

@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.

Collectives™ on Stack Overflow

MySQL: OUTER JOIN and COUNT()

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related