SQL query for selection from the items table, which is joined to the category table

0
votes

I have structure of the Category table:

  • id
  • title
  • is_active ( 0 or 1)
  • parent_id

Structure of the Item table:

  • id
  • title
  • is_active (0 or 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).The category and subcategory can also be active and not active.

Tell me please. I can not make a query to select items. I need to select items from the Items table that have the status is_active = 1, which are: 1) are joined to the main category, which has_active = 1. 2) And the most difficult: the items are joined to a subcategory with the status is_active = 1, which has a parent category with the status is_active = 1. Is this actually done with sql? I use query

    SELECT * FROM item LEFT JOIN categories 
ON item.category_id = categories.id
    WHERE item.is_active = 1 AND categories.is_active = 1 AND categories.parent_id IN 
    (SELECT id FROM categories WHERE parent_id IS NULL AND is_active = 1)

But it returns an empty result to me

1
mysqlsqljoincategories
What have you tried? SO is not a code writing service. We'll be glad to help when you';re stuck on a specific issue, but you have to at least show what you have tried. – Sloan Thrasher
Edited the question and added – tirael8
Possible duplicate of How to create a MySQL hierarchical recursive query – philipxy
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. – philipxy
Your query looks fine, so you need to check your assumptions about the data. First off, are there categories in the database you're querying where parent_id is Null and is_active = 1? If there are, are there any categories that are children of those categories? – Ann L.

1 Answers

0
votes
SELECT item.* FROM 
item INNER JOIN categories ON item.category_id = categories.id
WHERE item.is_active = 1 AND (categories.is_active = 1 AND categories.parent_id IN 
    (SELECT id FROM categories WHERE parent_id IS NULL AND is_active = 1)) OR ( item.is_active = 1 AND categories.parent_id IS NULL AND categories.is_active = 1 )