CODEWITHSUNDEEP
mysqlsqlcount
David
David

Reputation: 10738

mysql select row based on count of another table

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

Upvotes: 3

Views: 2118

Answers (3)

spencer7593
spencer7593

Reputation: 108370

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.

Upvotes: 1

JayDM
JayDM

Reputation: 1186

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.

Upvotes: 0

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

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

Upvotes: 7

Related Questions