sql to build categories menu that have products

Question

I have 3 tables (mysql)

products

product_id, title

product_id, category_id

The depth of the category tree is unknown. Is there a way to know with one query which categories have categories etc. that have products?

With your current data model I don't believe that this is possible in MySQL. You can find alternative models for hierarchies by looking for Joe Celko's book on trees and hierarchies or from some of the links in this post: stackoverflow.com/questions/4345909/mysql-recursion — Tom H
Why not just do the same on all the categories - if no products than you'll won't have nothing returns from your query, but it can be done with JOIN, I'll check it now — Itay P.
I use recursion to fetch first parent categories and then for each parent category fetch its subcategories etc to build the menu. But how can I know if a category doesn't have products so I don't display it? — Vagelis Ouranos

BuZz BuZz · Accepted Answer · 2011-11-04T18:51:50

Yep, try :

select c1.*, c2.* from categories c1, categories c2, products_categories pc where c1.category_id = c2.parent_id and pc_category_id = c1.category_id;

c1, c2 gives us the transitive closure for your tree, pc gives us the leaves of the tree.