SUB-TREE WITHIN A TREE in MySQL
In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).
Query to Create Table:
CREATE TABLE IF NOT EXISTS `Employee` ( `SSN` varchar(64) NOT NULL, `Name` varchar(64) DEFAULT NULL, `Designation` varchar(128) NOT NULL, `MSSN` varchar(64) NOT NULL, PRIMARY KEY (`SSN`), CONSTRAINT `FK_Manager_Employee` FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I have inserted a set of tuples (Query):
INSERT INTO Employee VALUES ("1", "A", "OWNER", "1"), ("2", "B", "BOSS", "1"), # Employees under OWNER ("3", "F", "BOSS", "1"), ("4", "C", "BOSS", "2"), # Employees under B ("5", "H", "BOSS", "2"), ("6", "L", "WORKER", "2"), ("7", "I", "BOSS", "2"), # Remaining Leaf nodes ("8", "K", "WORKER", "3"), # Employee under F ("9", "J", "WORKER", "7"), # Employee under I ("10","G", "WORKER", "5"), # Employee under H ("11","D", "WORKER", "4"), # Employee under C ("12","E", "WORKER", "4")
The inserted rows has following Tree-Hierarchical-Relationship:
A <---ROOT-OWNER /|\ / A \ B F //| \ \ // | \ K / | | \ I L H C / | / \ J G D E
I written a query to find relationship:
SELECT SUPERVISOR.name AS SuperVisor, GROUP_CONCAT(SUPERVISEE.name ORDER BY SUPERVISEE.name ) AS SuperVisee, COUNT(*) FROM Employee AS SUPERVISOR INNER JOIN Employee SUPERVISEE ON SUPERVISOR.SSN = SUPERVISEE.MSSN GROUP BY SuperVisor;
And output is:
+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) | +------------+------------+----------+ | A | A,B,F | 3 | | B | C,H,I,L | 4 | | C | D,E | 2 | | F | K | 1 | | H | G | 1 | | I | J | 1 | +------------+------------+----------+ 6 rows in set (0.00 sec)
[QUESTION] Instead of complete Hierarchical Tree, I need a SUB-TREE from a point (selective) e.g.: If input argument is B then output should be as below...
+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) | +------------+------------+----------+ | B | C,H,I,L | 4 | | C | D,E | 2 | | H | G | 1 | | I | J | 1 | +------------+------------+----------+
Please help me on this. If not query, a stored-procedure can be helpful. I tried, but all efforts were useless!
It my experienceI always got better answer from expert sides. And I think it was better decision to move question to Database Administrators. In all the cases, I am very thankful to stackoverflow and peoples who are active here. I really got solution for many problem that was very tough to find myself or any other web. – Grijesh Chauhan Dec 6 '12 at 16:43