How to SELECT immediate children and ancestors all in the same query

Question

I'm working with a tree structure in MySQL that is respresented using the nested sets model.

I'm hoping some of you sql experts can help me with building a SELECT query.

I would like to be able to match a set of nodes using LIKE. For each node that is matched, I also need a comma-delimmited list of the ancestors of that node, and a comma-delimmited list of the immediate children of that node.

I'm not really sure where to start with this - if such a thing is even possible in a single query. (Currently I am accomplishing this with a query inside a loop.) What I'm hoping for is a result set that might look something like this....

Starting with the string "qu" and querying the Table "Body" I get...

Node      | Parent Nodes               | Immediate Children
Quads       Leg, Lower Body, Muslces     Vastus Lateralus, Vastus Medialis, Rectus Femoris
Obliques    Core, Trunk, Muscles         Inner obliques, outer obliques

Any suggestions on how to accomplish this without looping queries would be much appreciated.

Answer 1

While I agree with nickf that this is bad and dirty, it's still fun, so here goes:

SELECT     base.left_id, base.ancestors, 
           GROUP_CONCAT(children.left_id) children
FROM       (
            SELECT     base.left_id
            ,          GROUP_CONCAT(ancestors.left_id) ancestors
            FROM       nested_set   base
            LEFT JOIN  nested_set   ancestors
            ON         base.left_id     BETWEEN ancestors.left_id 
                                            AND ancestors.right_id
            WHERE      base.name  LIKE '%criteria%'
            GROUP BY   base.left_id
           ) base                                    
LEFT JOIN  nested_set   children
ON         children.left_id BETWEEN base.left_id 
                                AND base.right_id
LEFT JOIN  nested_set   inbetween
ON         inbetween.left_id BETWEEN base.left_id 
                                AND base.right_id
AND        children.left_id  BETWEEN inbetween.left_id 
                                AND inbetween.right_id     
WHERE      inbetween.left_id IS NULL
GROUP BY   base.left_id

Basically, the trick is to solve it in two steps: first, solve the ancestors problem, and squash the ancestors to a list with, then, use this result to solve it for the children.

The ancestors part is relatively easy, it is the subquery in the from clause in my solution. The children is a bit harder. It works by taking all descendents, and then requiring that there do not exist any nodes between the base node and the descendents, which basically restricts the descendents to only the children.

There are other variations to this strategy to solve this - for example you can do the children first, and solve the ancestors using a subquery in the SELECT list.