14630

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

Question:

I have table definition like below:

<ul><li>Place<br /> (id, name)</li> <li>Review<br /> (id, userid, placeid)</li> <li>Favorite <br />(id, userid, placeid)</li> <li>Photo<br /> (id, url, placeid) <br /> where placeid is foreign key to the id of Place table.</li> </ul>

On that table, I want to derive this kind of information:<br />- 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.

Answer1:

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

Answer2:

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

Recommend

  • “Multiple include guards may be useful for” what, exactly?
  • Run StormCrawler in local mode or install Apache Storm?
  • htaccess protect all subfolders & files
  • Bootstrap multi select grouping dynamicaly using array
  • Missing App Store icon codename one build
  • Hyperledger fabric's ChannelCreationPolicy
  • Create a mobile app that just points to URL
  • JavaFX resize children relative to parent
  • How to change selected item position to top in Recyclerview?
  • Lodash cloneDeepWith to omit undefined
  • Preloading webView doesn't work - trying to reduce loading time
  • Limit number of button clicks
  • Turn Inline OnClick to Click Jquery
  • using vtkTimerCallback with QVTKRenderWindowInteractor not working
  • Hibernate Idempotent Update
  • Webdriver 3.14 IE11: session lost when click link/button that opens a window/popup
  • Anaconda + Apache + mod_wsgi + Ubuntu
  • Azure NodeJS Error: ENOENT, open 'D:\\home\\site\\wwwroot\\bin\\views\\'
  • Misplaced CAGradientLayer on iPhone 6s
  • Draw string with normalized scientific notation (superscripted )
  • Use 2D Text into 3D scenes in JavaFX results in blurry texts
  • Calling a flash ExternalInterface in swiffyobject
  • Wicket - getting body of markup element
  • How to search for a method or variable name within android project?
  • Unity Resources.load() won't work with external dll
  • separate tokens in batch file
  • opencv deskewing a contour
  • What does “T extends Junk” mean in a generic class in Java?
  • How to redirect into different page by user type in php and mysql
  • Getting the type of a “Type” in C# reflection
  • Wireshark Display Filter for Unique Source/Destination IP and Protocol
  • What is the difference between dynamically creating a script tag and statically embed a script tag?
  • How do I use libcurl to printf a remote FTP directory listing?
  • Regex not working in java 1.5
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • Support of :after in IE7
  • how do i compare two rows and store the similarities of the two rows in another column
  • Using redis as an LRU cache for postgres
  • What is the best way to cache and reuse immutable singleton objects in Java?
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner