Select query multiple ID's

Question

I have 2 tables: categories and items.

table: categories

id | parent_id | name
------------------------------
1  | 0         | drinks
2  | 1         | soft drinks
3  | 1         | beer

and

table: items

id | category_id | name
----------------------------
1  | 1           | water
2  | 2           | coca cola
3  | 2           | pepsi cola
4  | 3           | stella artois

What I want to do? I want to count all the items in a category or in a subcategory of that category.

So if I select for the category "drinks" I want to count all the items in the category "drinks" but also all the items in the subcategories "soft drinks" and "beer".

I think it has something to do with a left join but I cannot figure it out.

Thanks in advance!

you'd need a self-join on the categories table for the parent'd child stuff. and can your items ever be part of a "parent" category - will there ever be something that's got category_id=0, "drinks"? — Marc B, Nov 01 '16 at 14:47
@MarcB, since you're here. I have commented on a answer of yours yesterday. [Take a look](http://stackoverflow.com/a/3936986/4802649) — Phiter, Nov 01 '16 at 14:52

MonkeyZeus · Accepted Answer · 2016-11-01T14:58:52.357

0

I think this will work.

select c.name,
       (select count(*)
          from items i
         where i.category_id in (select c2.id
                                   from category c2
                                  where c.id in (c2.id, c2.parent_id)
       ) as total
  from category c

This should produce:

name        | total
-------------------
drinks      | 4
soft drinks | 2
beer        | 1

edited Nov 01 '16 at 14:58

answered Nov 01 '16 at 14:52

MonkeyZeus

20,375
4
36
77

Select query multiple ID's

1 Answers1