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 submission开发者_JAVA百科s but this is obviously a very inefficient solution. Any help is appreciated.

Try this:

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

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