Select rows that match multiple rows in related table

Question

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.

Hi can you please tell me how Result for category_ids '1,2,3' is 3 because there is not a single item who is in all 3 category..thanks — Er. Anurag Jain
– Er. Anurag Jain, Commented May 17, 2012 at 11:11

Er. Anurag Jain · Accepted Answer · 2012-05-17 11:59:01Z

2

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

answered May 17, 2012 at 11:59

Er. Anurag Jain

1,7931 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select rows that match multiple rows in related table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related