How to query a many-to-many relationship-CodePudding

3 Tables: Fruits(id, name) Buckets(id, name) Bucket_Fruit(id, fruit_id, bucket_id, count)

e.g There's a bucket Bucket1 having 2 apples and 1 banana:

Fruits.id	Fruits.name
1	Apple
2	Banana

Buckets.id	Buckets.name
1	Bucket1

Bucket_Fruit.id	Bucket_Fruit.fruit_id	Bucket_Fruit.bucket_id	Bucket_Fruit.count
1	1	1	2
2	2	1	1

Question is: how to query the bucket which only have exactly 2 apples and 1 banana? I can guarantee that there wont be 2 buckets which have same fruits.

CodePudding user response：

Try this:

select distinct bucket_id 
from Bucket_Fruit b
where exists (select * from Bucket_Fruit where bucket_id=b.bucket_id 
                                        and fruit_id=(select id from Fruits where name='apple') 
                                        and count=2)
    and exists (select * from Bucket_Fruit where bucket_id=b.bucket_id 
                                        and fruit_id=(select id from Fruits where name='banana') 
                                        and count=1)
    and bucket_id in (select bucket_id from Bucket_Fruit group by bucket_id having count(fruit_id)=2)
;