mysql> SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+----------------------+
10 rows in set
mysql> SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
6 rows in set
mysql> SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft ASC;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+----------------------+
4 rows in set
mysql> SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+----------------------+-------+
10 rows in set
mysql> SELECT CONCAT( REPEAT('|--
', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name |
+-------------------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS |
| |--|--|--FLASH |
| |--|--CD PLAYERS |
| |--|--2 WAY RADIOS |
+-------------------------+
10 rows in set
mysql> 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.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
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
5 rows in set
mysql> 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.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
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
5 rows in set
mysql> 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.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;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
4 rows in set
mysql> 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.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;
+--------------+-------+
| name | depth |
+--------------+-------+
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+--------------+-------+
3 rows in set
mysql> CREATE TABLE product
(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);
Query OK, 0 rows affected
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
+----------------------+---------------------+
| name | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS | 10 |
| TELEVISIONS | 5 |
| TUBE | 2 |
| LCD | 1 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 5 |
| MP3 PLAYERS | 2 |
| FLASH | 1 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 1 |
+----------------------+---------------------+
10 rows in set
mysql> LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
Query OK, 0 rows affected
+-----------------+
| @myRight := rgt |
+-----------------+
| 9 |
+-----------------+
1 row in set
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0
Query OK, 5 rows affected
Rows matched: 5 Changed: 5 Warnings: 0
Query OK, 1 row affected
Query OK, 0 rows affected
mysql> SELECT CONCAT( REPEAT('|--', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name |
+-------------------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--GAME CONSOLES |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS |
| |--|--|--FLASH |
| |--|--CD PLAYERS |
| |--|--2 WAY RADIOS |
+-------------------------+
11 rows in set
mysql> LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = '2 WAY RADIOS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;
Query OK, 0 rows affected
+----------------+
| @myLeft := lft |
+----------------+
| 19 |
+----------------+
1 row in set
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0
Query OK, 1 row affected
Query OK, 0 rows affected
mysql> SELECT CONCAT( REPEAT('|--', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name |
+-------------------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--GAME CONSOLES |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS |
| |--|--|--FLASH |
| |--|--CD PLAYERS |
| |--|--2 WAY RADIOS |
| |--|--|--FRS |
+-------------------------+
12 rows in set
mysql> LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
Query OK, 0 rows affected
+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
| 10 | 11 | 2 |
+----------------+-----------------+---------------------------+
1 row in set
Query OK, 1 row affected
Query OK, 7 rows affected
Rows matched: 7 Changed: 7 Warnings: 0
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0
Query OK, 0 rows affected
mysql> SELECT CONCAT( REPEAT('|--', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name |
+-------------------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS |
| |--|--|--FLASH |
| |--|--CD PLAYERS |
| |--|--2 WAY RADIOS |
| |--|--|--FRS |
+-------------------------+
11 rows in set
mysql> LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;
Query OK, 0 rows affected
+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
| 11 | 14 | 4 |
+----------------+-----------------+---------------------------+
1 row in set
Query OK, 2 rows affected
Query OK, 5 rows affected
Rows matched: 5 Changed: 5 Warnings: 0
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 0 rows affected
mysql> SELECT CONCAT( REPEAT('|--', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name |
+-------------------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--PORTABLE ELECTRONICS |
| |--|--CD PLAYERS |
| |--|--2 WAY RADIOS |
| |--|--|--FRS |
+-------------------------+
9 rows in set
mysql> LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
DELETE FROM nested_category WHERE lft = @myLeft;
UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;
UNLOCK TABLES;
Query OK, 0 rows affected
+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
| 10 | 17 | 8 |
+----------------+-----------------+---------------------------+
1 row in set
Query OK, 1 row affected
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0
Query OK, 0 rows affected
mysql> SELECT CONCAT( REPEAT('|--', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------+
| name |
+-----------------+
| ELECTRONICS |
| |--TELEVISIONS |
| |--|--TUBE |
| |--|--LCD |
| |--|--PLASMA |
| |--CD PLAYERS |
| |--2 WAY RADIOS |
| |--|--FRS |
+-----------------+
8 rows in set
mysql> SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'LCD';
+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
| 5 | 6 | 2 |
+----------------+-----------------+---------------------------+
1 row in set
mysql>