0

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

tirael8
  • 213
  • 4
  • 14
  • 2
    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 Jun 16 '18 at 20:01
  • Edited the question and added – tirael8 Jun 16 '18 at 20:15
  • 2
    Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – philipxy Jun 16 '18 at 20:16
  • 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 Jun 16 '18 at 20:17
  • 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. Jun 16 '18 at 20:43
  • One thing to check about the data would be: you're assuming a two-level hierarchy of categories: the categories assigned to items and the parent, top-level categories. If the categories actually assigned to items are more than one level below the top-level categories, you won't get any records returned. – Ann L. Jun 16 '18 at 20:45
  • If that's the case, you want to look into hierarchical recursive queries, as suggested by @philipxy. – Ann L. Jun 16 '18 at 20:46
  • Please read & act on [mcve]. – philipxy Jun 16 '18 at 20:48

1 Answers1

0
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 )
tirael8
  • 213
  • 4
  • 14
  • While this code snippet may be the solution, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Narendra Jadhav Jun 17 '18 at 07:28