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.