What is the proper query to get all the children in a tree?
- by Nathan Adams
Lets say I have the following MySQL structure:
CREATE TABLE `domains` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`domain` CHAR(50) NOT NULL,
`parent` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
insert into `domains`(`id`,`domain`,`parent`) values (1,'.com',0);
insert into `domains`(`id`,`domain`,`parent`) values (2,'example.com',1);
insert into `domains`(`id`,`domain`,`parent`) values (3,'sub1.example.com',2);
insert into `domains`(`id`,`domain`,`parent`) values (4,'sub2.example.com',2);
insert into `domains`(`id`,`domain`,`parent`) values (5,'s1.sub1.example.com',3);
insert into `domains`(`id`,`domain`,`parent`) values (6,'s2.sub1.example.com',3);
insert into `domains`(`id`,`domain`,`parent`) values (7,'sx1.s1.sub1.example.com',5);
insert into `domains`(`id`,`domain`,`parent`) values (8,'sx2.s2.sub1.example.com',6);
insert into `domains`(`id`,`domain`,`parent`) values (9,'x.sub2.example.com',4);
In my mind that is enough to emulate a simple tree structure:
.com
|
example
/ \
sub1 sub2
ect
My problem is that give sub1.example.com I want to know all the children of sub1.example.com without using multiple queries in my code.
I have tried joining the table to itself and tried to use subqueries, I can't think of anything that will reveal all the children.
At work we are using MPTT to keep in hierarchal order a list of domains/subdomains however, I feel that there is an easier way to do it.
I did some digging and someone did something similar but they required the use of a function in MySQL. I don't think for something simple like this we would need a whole function.
Maybe I am just dumb and not seeing some sort of obvious solution.
Also, feel free to alter the structure.
