So I'm using MySQL and storing my category structure based on the Nested Set / Modified Pre-Order Tree Traversal model, in a table named "nested_category" which has fields: category_id, name, lft, rgt, published

published is either 1 or 0... If it's 1 then the category will show upon the live site. If it's 0 then it won't show on the live site, and more importantly any children of that unpublished category won't show on the live site either.

I'm having problems writing a query to list all the categories that have published=1, and ignore all categories that are descendants of a category which has published=0.

At the moment I'm using:

SELECT category_id, lft, rgt FROM nested_category WHERE published = 1
I really can't work out how to get it to ignore "child" categories when a parent is "unpublished".

I'm also trying to link this to my "new_products" table which has fields: product_id, name, stock, price, category_id, so that I can write a query to select all products that have published=1 and are part of a "published" category. I have got this far:

select @myRight := rgt, @myLeft := lft 
from nested_category where name="ELECTRONICS";

select productId, productName, new_products.category_id, 
price, stock, new_products.published 
from new_products 
inner join ( 
    select category_id, lft, rgt from nested_category 
    where published = 1
) cat 
on new_products.category_id = cat.category_id 
and cat.lft >= @myLeft 
and cat.rgt <= @myRight 
and new_products.published = 1 
order by productName asc
Since the above query uses my first query, it doesn't return any "unpublished" categories or products, but it doesn't take into account when a "published" category is a descendant of an "unpublished" category. Hope this makes sense!