how to get collection based on number of duplicate foreign keys? I have a batch table, and an item table, the relationship is the batch has many items (item has a batch_id foreign key).
How would I make a query that will find the batches which have more than 100 items?
SELECT b.* FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) &gt; 100

To get the count by batch_id
SELECT b.batch_id, COUNT(i.batch_id)
FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) &gt; 100