如何使用LEFT JOIN mysql从另一个表派生count（*）

Question

I have table definition like below:

• Place
  (id, name)
• Review
  (id, userid, placeid)
• Favorite
  (id, userid, placeid)
• Photo
  (id, url, placeid)
  where placeid is foreign key to the id of Place table.

On that table, I want to derive this kind of information:
- placeid, place name, totalReview, totalFavorite, totalPhoto.

I got stucked. My progress currently I can derive information just from 1 table, like I can know totalReview of place, by using this mysql statement: SELECT p.*, count(r.id) as totalReview from Place p left join Review r on p.id = r.placeid group by p.id. But, I don't know how I can derive the totalFavorite and totalPhoto.

Answer 1

You need to aggregate each table separately. Here is one solution:

SELECT p.*, 
       totalreview, 
       totalfavorite, 
       totalphoto 
FROM   place p 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalReview 
                        FROM   review 
                        GROUP  BY placeid) r 
                    ON p.placeid = r.placeid 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalFavorite 
                        FROM   favorite 
                        GROUP  BY placeid) f 
                    ON p.placeid = f.placeid 
       LEFT OUTER JOIN (SELECT placeid, 
                               Count(*) AS totalPhoto 
                        FROM   photo 
                        GROUP  BY placeid) ph 
                    ON p.placeid = ph.placeid