mysql - find grandparents/grandchildren

Another interesting set of queries are parent, grandparent queries.

To find all the grand children nodes do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

To find all the grand parents with more than 2 grand children do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

To produce the full family tree of a grandchild node do this

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

To find all nodes whos grandparent(and possibly parent) is unknown do this;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;

Here is my full test sql.. try it out

create table kids (id integer, parent_id integer);
describe kids;
insert into kids values (1,null);
insert into kids values (3,1);
insert into kids values (4,2);
insert into kids values (5,2);
insert into kids values (6,4);
insert into kids values (7,2);
insert into kids values (8,7);
insert into kids values (9,7);
insert into kids values (10,7);
insert into kids values (11,3);
select * from kids;
select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;