0

I have 2 tables: shop_products and shop_category.
shop_products: id, name, category_id
shop_category: id, name, parent_id, active. Active can be 'true' or 'false'. Categories are nested like parent>subcat>subsubcat>...
How is it possible to get all products where top parent category active is 'true'?
I tried to use JOIN. But it takes only closest category, not the top category of tree. 

SELECT * FROM shop_products 
INNER JOIN shop_category as sc1 ON (shop_products.category_id=sc1.id)
INNER JOIN shop_category as sc2 ON (sc1.parent_id=sc2.id)
WHERE sc2.active=1

UPD: Here is sqlfiddle

I expect to get nothing. Because the top category 'Cat1' has active false

Nikita
  • 31
  • 5
  • Can you edit your question to include sample data and expected output? Specifically, what is the value of parent_id for a top category? – Nick Jul 23 '18 at 12:28
  • 1
    Adjacency List Model (parent_id model) isn't the best method to store hierarchical data in SQL because you need to use a recursive common table expression (CTE) (only MySQL 8.0) or some other tricks to simulate recursive SQL ( https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) or ( https://stackoverflow.com/questions/3752078/recursive-stored-functions-in-mysql ) or alot off `LEFT JOIN`s for every path one `LEFT JOIN`.. Some other better options to store hierarchical data in SQL are "Nested Set Model" ("Modified Preorder Tree") or a "Closure table" – Raymond Nijland Jul 23 '18 at 12:31
  • @nick, I've updated the post – Nikita Jul 23 '18 at 12:47
  • [Required read when working with categories in mysql](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/). Usage of this makes what you're trying a whole lot easier. (Assumes you're developing categories yourself of course) – rkeet Jul 23 '18 at 12:51

1 Answers1

0

Use Below query: 

SELECT s.* FROM shop_products as s WHERE EXISTS (
  SELECT id FROM shop_category as sc WHERE sc.id = s.category_id AND EXISTS(
  SELECT id FROM shop_category as sc1 WHERE sc.id = sc1.id AND sc1.active = 1

  )
);
Devraj verma
  • 407
  • 3
  • 14