CODEWITHSUNDEEP
sqlms-access
user2218255
user2218255

Reputation: 62

Retrieve item which is sold more than twice

I have 3 tables;sale, sale_detail, item, and their relationship are sale 1:M sale_detail and item 1:M sale

in item table

item_id
1
2
3
4

in sale table 

sale_id
1
2
3

in sale_detail table

sale_id    items_id
1          1
1          2
1          3
2          2
2          3

I want to retrieve item id which is sold more than twice.

Upvotes: 0

Views: 60

Answers (1)

Matt Busche
Matt Busche

Reputation: 14333

You can use a group by with a having attribute

SELECT i.item_id
FROM sale_detail sd
    INNER JOIN item i ON sd.items_id = i.item_ID
    INNER JOIN sale s ON sd.sale_id = s.sale_id
GROUP BY i.item_id HAVING COUNT(s.sale_id) > 2

Upvotes: 3

Related Questions