Nested Set Model: Query node childs only 1 level below AND all parents above

I am using Nested Set Model with MySQL to create hierarchical tree model.

I have successfully managed to get Node and all its Childs 1 level below (I need to get only 1 sub-level)

However, I would like to have not only all Childs 1 level below, but all Parents above that Node too.

Is it possible to modify current query to get what I want?

This is my code (actually it's taken from this, non-working, site: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ but mine is exactly the same):

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
    SELECT node.name, (COUNT(parent.name) - 1) AS depth
    FROM nested_category AS node,
    nested_category AS parent
    WHERE node开发者_如何学C.lft BETWEEN parent.lft AND parent.rgt
    AND node.name = 'PORTABLE ELECTRONICS'
    GROUP BY node.name
    ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

Tree structure looks like this:

Electronics
--Televisions
----LCD
--Portable Electronics
----MP3 Players
------Flash
----CD Players

Using my current code above, I get this:

--Portable Electronics
----MP3 Players

But I need to get something like this:

Electronics
--Portable Electronics
----MP3 Players

I will be using this query later in PHP, so I can use PHP-based solution (or part of it) too.

Thank you

I think you want to split this up in multiple queries. First the 'PORTABLE ELECTRONICS' item with all its childs, like you're doing now. After that, fetch the parents is easy, just fetch all elements WHERE left < [PE-left] AND right > [PE-right].

I managed to solve the problem by using multiple queries.

First query retrieves all parents above node (as Rijk suggested) and second query retrieves everything below the node (code, posted in original post). Then using UNION ALL I combined everything into one result set.

However I could not use depth column to make indentation anymore, so to make page formating for childs, i used php-based function to indent nodes ( http://www.sitepoint.com/hierarchical-data-database-2/ )

Note: I had to add if/else statement at the end of this code to indent only parent nodes and not everything.

I had the same issue. This seemed to work.

SELECT parent.name
FROM nested_category AS node,nested_category AS parent,nested_category AS midpoint
WHERE (node.lft BETWEEN parent.lft AND parent.rgt) AND (node.lft BETWEEN midpoint.lft AND midpoint.rgt) AND midpoint.name='PORTABLE ELECTRONICS'
GROUP BY parent.name
ORDER BY node.lft;