CODEWITHSUNDEEP
mysqlsqljoincategories
tirael8
tirael8

Reputation: 213

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

I have structure of the Category table:

Structure of the Item table:

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

Upvotes: 0

Views: 1796

Answers (1)

tirael8
tirael8

Reputation: 213

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 )

Upvotes: 0

Related Questions