CODEWITHSUNDEEP
mysqlselect
Mr_Chimp
Mr_Chimp

Reputation: 6907

Select rows that match multiple rows in related table

I have three tables:

item

id   name   etc
--------------------
1    Rex   
2    Fido
3    Geoff

category

id   name
------------
1    Dogs
2    Humans
3    Mammals

category_item

category_id  item_id
--------------------
1            1
3            1
1            2
3            2
2            3
3            3

I also have an array of category ids. I would like to count the number of items that are related to ALL of the categories in the array.

For example...

Category_ids    Result
----------------------
1,2             0
2,3             1
1,2,3           0

Pretty sure I'm gonna kick myself when I figure this one out.

Upvotes: 2

Views: 731

Answers (1)

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

Please try query given below..

select count(*) AS Result from (
         SELECT count(item_id) FROM category_item
                             WHERE 
                            category_id in (2 ,3)
                            GROUP by item_id 

                              HAVING count(*) = 2
                             ) AS temp

In this query put count(*) value equal to total number of category_id for example if you are checking for category_ids 1,2,3 then put having count(*) = 3. In this query let assume you provide category_id 1 and 2 then it will fetch total number of existence of item_id with

I hope this query will helpful for you.

thanks

Upvotes: 2

Related Questions