Select ancestors and immediate children of a node in a nested set tree using MYSQL

Question

Hoping some of you mysql experts can help me out.

I have searchtag data that is stored in a nested set.

TABLE searchTags

searchTagID
searchTag
lft
rgt

(I am using nested sets because there are times when I need to easily select entire branches of the tree.)

I would like to construct a query that will return a resultset of nodes matched with LIKE, the ancestors of each of these nodes, and the immediate children of each of these nodes.

For example, if this is my nested set...

                          1_tagA_22
     2_tagB1_11          12_tagB2_13           14_tagB3_21
3_taC1_4   5_taC2_10                                   15_tagC3_20
               6_tagD1_9                        16_tagD2_17    18_tagD3_19
                  7_tagE1_8

...and for my search needle I have LIKE CONCAT('%','tagc','%'), I would like to return something that looks like this:

searchTag   |   ancestors   |  immediateChildren
tagC1          tagB1,tagA       NULL
tagC2         tagB1,tagA       tagD1
tagC3          tagB3,tagA       tagD2,tagD3

I am currently doing this with multiple queries. First I select a resultset of searchtags and their ancestors. Then I loop through this resultset and for each result, I do another select to get the immediate children. (I am including these queries below, for reference.)

This method works, but I have a feeling it is an inefficient solution and is likely to blow up sooner or later. :) I am wondering if there is a better way of doing this - ie is there a way to combine all of this into a single SELECT statement that is more efficient?

Any advice would be much appreciated.

Thanks!

Reference: I use the following to select the searchtag and the path of ancestors...

    SELECT 

        node.searchTag, 

        GROUP_CONCAT(parent.searchTag 
        ORDER BY parent.lft DESC
        SEPARATOR '; ') AS ancestors 


    FROM 

        searchTags AS node,
        searchTags AS parent

    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND parent.lft < node.lft
    AND node.searchTag LIKE CONCAT('%','tagc','%')
    GROUP BY node.searchTagID;

...and then I loop through this resultset and for each result run another query to get the immediate children (using mrbinky3000's excellent method):

SELECT lft, rgt INTO @parent_left, @parent_right FROM searchTags WHERE searchTagID = $id;

SELECT 

    GROUP_CONCAT(child.searchTag SEPARATOR "; ") as searchTag

FROM searchTags AS child
LEFT JOIN searchTags AS ancestor ON
    ancestor.lft BETWEEN @parent_left+1 AND @parent_right-1 AND
    child.lft BETWEEN ancestor.lft+1 AND ancestor.rgt-1
WHERE
    child.lft BETWEEN @parent_left+1 AND @parent_right-1 AND
    ancestor.searchTagID IS NULL;

Answer 1

Have a read of these good articles on nested sets in MySQL http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/