Select the Items in Nested set

Question

I have the following nested set.

The cat.table:

cat.ID Name Lft Rgt
  1     A    1   8
  2     B    2   5
  3     C    3   4
  4     D    6   7
  5     E    9   14
  6     F   10   11
  7     G   12   13

OUTPUT: In brackets the count of the items. (In 'A' will count that items, which are in its node, too - B, C and D)

 A (7)
 -B (3)
 --C (2)
 -D (1)
 E (3)
 -F (1)
 -G (2)

The Items table:

ID Cat.ID  Name
1    1     item1
2    2     item2
3    2     item3
4    2     item4
5    3     item5
6    3     item6
7    4     item7
8    6     item8
9    7     item9
10   7     item10

If I select the Cat A than I would like to select that items which are in the B, C and D, too. If I select the B, than show the item from B and C. If I select the F than only show from F... In the Items table I don't have left and right values only tha Cat.ID.

How do I know that if I select the "A" category, which items are in "B", "C" and "D" too?

EDIT: E.g: If I select B than show the items from B and C (the B is parent of C and C is node of B) - output:

item2
item3
item4
item5
item6

tutorial: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ The calculation after the heading: "Aggregate Functions in a Nested Set" — Gery, Jan 12 '13 at 08:08
@Gery What is your expected output based on these sample data? — bonCodigo, Jan 12 '13 at 08:17
I can't understand your question. The structure is clear after reading the linked article, but what is the question? — remigio, Jan 12 '13 at 08:31
I need a query which select the (e.g.) "A" category than select that items from the Items table which are not only belongs to the "A" category but the "A", "B", "C" and "D", because the "A" the main category and B, C and D the A's nodes... — Gery, Jan 12 '13 at 08:36
@Gery btw that count table in the middle seem to add no light to the question.. — bonCodigo, Jan 12 '13 at 08:44

score 1 · Accepted Answer · answered Jan 12 '13 at 08:56

The answer is in the Retrieving a full tree paragraph of the linked article.

SELECT Id,Name FROM Items WHERE cat_id IN (
SELECT cat_id
FROM category AS node,
    category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND parent.name = 'A')

Select the Items in Nested set

1 Answers1