CODEWITHSUNDEEP
sql
Andy Hin
Andy Hin

Reputation: 31893

SQL: Only select if minimum number of rows is met

I have two tables: "Cities" and "Submissions".

I want to select a list of cities that have at least n Submissions. What is the best way to go about this? Also relevant is that most of the cities will have less than n Submissions.

I've tried selecting all the cities and then refiltering the list by doing a COUNT(*) query against submissions but this is obviously a very inefficient solution. Any help is appreciated.

Upvotes: 0

Views: 2341

Answers (2)

Tim
Tim

Reputation: 5421

   select cityname, count(submissionid) as submissioncount
   from cities inner join submissions
   on submissions.cityid = cities.cityid
   group by cityname
   having count(submissionid) >= {yourdesiredcount}

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838336

Try this:

SELECT Cities.id
FROM Cities
JOIN Submissions ON Cities.id = Submissions.CityId
GROUP BY Cities.id
HAVING COUNT(*) >= n

Upvotes: 4

Related Questions