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 ?"

Andrius Naruševičius · Accepted Answer · 2012-08-08 14:02:30Z

7

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

answered Aug 8, 2012 at 14:02

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JayDM Over a year ago

This one is prettier than mine. Still, make sure that you have an index on the Photos table though.

spencer7593 · Accepted Answer · 2012-08-08 14:15:36Z

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.

JayDM · Accepted Answer · 2012-08-08 14:05:34Z

To get the rows in Photos where there are at least five per PostID...

select PostID, count(PostID) as occurances from Photos group by PostID

To get only those where there are at least five...

select PostID from (select PostID, count(PostID) as occurances from Photos group by PostID) p where occurances >= 5

To get the blog posts that correspond...

select * from BlogPosts where ID in (select PostID from (select PostID, count(PostID) as occurances from Photos group by PostID) p where occurances >= 5);

Or, really - you should first create a view that only returns the PostIDs from Photos that have more than five occurances:

create view atLeastFive as select PostID from (select PostID, count(PostID) as occurances from Photos group by PostID) p where occurances >= 5

Then your query looks a little more presentable:

select * from BlogPosts where ID in (select PostID from atLeastFive)

Make sure that your Photos table has an index on PostID or the performance will end up suffering.

Collectives™ on Stack Overflow

mysql select row based on count of another table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related