title: SQL query for selection from the items table, which is joined to the category table
body: <p>I have structure of the Category table:</p>
  
  <ul>
  <li>id</li>
  <li>title</li>
  <li>is_active ( 0 or 1)</li>
  <li>parent_id</li>
  </ul>
  
  <p>Structure of the Item table:</p>
  
  <ul>
  <li>id</li>
  <li>title</li>
  <li>is_active (0 or 1)</li>
  <li>category_id</li>
  </ul>
  
  <p><img src="https://image.ibb.co/emzXEd/33.jpg" alt="table structure"></p>
  
  <p>Tables are associated with the category_id field by a one-to-many relationship. Those. 1 category can have many items.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <pre><code>    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)
  </code></pre>
  
  <p>But it returns an empty result to me</p>