how to get collection based on number of duplicate foreign keys?

Question

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?

Thirumal · Answer

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) > 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) > 100

how to get collection based on number of duplicate foreign keys?

One Answer

Add your own answers!

Ask a Question