CODEWITHSUNDEEP
phpmysqlsql
Agung Pratama
Agung Pratama

Reputation: 3784

How I can derive count(*) from another table using LEFT JOIN mysql

I have table definition like below:

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.

Upvotes: 1

Views: 984

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 4

Ehsan Khodarahmi
Ehsan Khodarahmi

Reputation: 4922

This is a simple way to do this:

SELECT
p.id, p.name,
(SELECT COUNT(*) FROM Review r WHERE r.placeId=p.id) AS totalReview
(SELECT COUNT(*) FROM Favorite f WHERE f.placeId=p.id) AS totalFavorite
(SELECT COUNT(*) FROM Photo ph WHERE ph.placeId=p.id) AS totalPhoto
FROM Place p

Upvotes: 0

Related Questions