mysql select row based on count of another table

Question

I have a table of blog posts in one table. In a separate table i have records of images that are stored in a directory that are tied to specific blog posts.

How can i write a query that selects only blog posts that have at least 5 photos?

The BlogPosts table has an 'ID' column. The Photos table has a 'PostID' column that corresponds to the the BlogPosts' 'ID' column.

$query = "SELECT * FROM BlogPosts WHERE ?"

Answer 1

SELECT * 
FROM BlogPosts 
WHERE ID IN 
    (SELECT PostID FROM photos GROUP BY PostID HAVING count(*) >= 5)

Answer 2

Here's an example of a statement that should return the specified resultset.

SELECT b.*
  FROM BlogPosts b
  JOIN (SELECT p.PostID
          FROM photos p
         GROUP BY p.PostID
        HAVING COUNT(1) >= 5
       ) c
    ON c.PostID = b.ID

This query makes use of an inline view, aliased as c. The inline view is returning a distinct list of PostID values from the photos table for which there are at least five rows in the photos table. Then it "joins" that resultset to the BlogPosts table, so it only returns rows that have an ID that matches a PostID in the subquery.

NOTE: On MySQL, this form, using the inline view, will (usually) perform better than using an IN (subquery) predicate.

NOTE: Please quell any urge you have to "hide" this in a view, just for the sake of anyone else that later needs to read this code later, and figure out what the query is doing.