Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Matt Bowlby
Matt Bowlby
8,806 Points
Posted by Matt Bowlby
Matt Bowlby
8,806 Points

SQL Query from Two Tables

I am having trouble putting together an SQL query. I have a database with a table for Locations (coffee shops, restaurants, etc.) and a table for LocationStatus (open, closed, etc.). The Locations table and the LocationStatus table are linked by id_Location in the LocationStatus table. It is possible that a location has more than one location status entry, but the most recent entry reflects the current status.

I need to return the total number of restaurants with each possible status. For example:

open, 43 closed, 32

How do I put together this query? I tried the following but it's not getting me what I want:

SELECT status, COUNT (id_Location) FROM LocationStatus GROUP BY status

Thanks.

2 Answers

Batuhan Ozgur Ozdemir
Batuhan Ozgur Ozdemir
4,658 Points
Batuhan Ozgur Ozdemir
Batuhan Ozgur Ozdemir
4,658 Points

I'm not sure but I think you should use Inner Join expression for this.

Matt Bowlby
Matt Bowlby
8,806 Points
Matt Bowlby
Matt Bowlby
8,806 Points

Thanks for the suggestion. I actually posted the same question in more detail on StackOverflow:

http://stackoverflow.com/questions/32056616/sql-query-from-three-related-tables

I came up with this so far:

SELECT id_LocationType, status, COUNT(DISTINCT id_Locations) FROM Locations join LocationStatus ON Locations.id = LocationStatus.id_Location GROUP BY id_LocationType, status

However, a Location can have many statuses. For example, a Location status can be updated multiple times in a two week period. A record is created in the LocationStatus table each time a new status is created. I want to count on the most recent status update. The above query counts all status updates. Any ideas on how to adjust it?