SQL query for a selection from a table with categories and subcategories

0
votes

Structure of the Category table:

  • id
  • title
  • parent_id

Structure of the Item table:

  • id
  • title
  • is_active (0 или 1)
  • category_id

table structure

Tables are associated with the category_id field by a one-to-many relationship. Those. 1 category can have many items.

The two-level hierarchy in the Category table. This defines the main categories and subcategories. Categories are those records in which parent_id = NULL. And subcategories, these are those records in which parent_id = to some id.

One Item can belong to both the main category (where parent_id = null) and the subcategory (child). Item can be active and not active (0 or 1).

You need to make a query and select from the Category table all the main categories (which have parent_id = null) that have active items (is_active = 1) and which have subcategories also with active itemes. i.e If the child subcategory has items with is_active = 0, then do not display this category.

I could only choose the main categories in which only active items:

SELECT categories.title, count(analyses.id) FROM items
            INNER JOIN categories on items.category_id = categories.id
            WHERE categories.parent_id IS NULL
            AND categories.is_active = 1
            GROUP BY analyses.category_id
            ORDER BY analyses_categories.title

But with subcategories can not cope anymore, tell me please who has more experience.

1
mysqlsqlselectcategories
What have you tried? Look into using join and exists. – sgeddes
School assignment? – Joakim Danielson
Expected results would be helpful as well as trying it yourself first. – t..
I can not think of a way to do this in 1 query. I just got to choose the main categories, in which there are only active items. – tirael8
Your objectives in this are not very clear. Try to clear up what you are trying to achive in the final statement. Also show us what you have come up with first, so we can point you in a direction instead of doing it for you. – ObieMD5

1 Answers

1
votes

It's a little unclear what you are trying to count (just the active items associated with the parent?), but I would use exists to find out which have children with active items as well:

select c.title, count(*)
from categories c
    join item i on i.category_id = c.id 
where c.parent_id is null and i.is_active = 1 and exists (
    select 1
    from categories c2
        join item i on c2.id = i.category_id
    where c2.parent_id = c.id and i.is_active = 1
)
group by c.title