drop table if exists tab2\\
drop table if exists tab1\\

create table tab1 (id int not null auto_increment primary key, name text)\\
create table tab2 (id2 int not null auto_increment primary key, important_data text, parent_id int,
                   foreign key (parent_id) references tab1(id) on delete cascade)\\

insert into tab1(name) values('parent one')\\
insert into tab1(name) values('parent two')\\
insert into tab1(name) values('parent three')\\

insert into tab2(important_data, parent_id) values('one child one', 1)\\
insert into tab2(important_data, parent_id) values('two child one', 1)\\
insert into tab2(important_data, parent_id) values('three child one', 1)\\
insert into tab2(important_data, parent_id) values('four child one', 1)\\
insert into tab2(important_data, parent_id) values('one child two', 2)\\
insert into tab2(important_data, parent_id) values('two child two', 2)\\
insert into tab2(important_data, parent_id) values('three child two', 2)\\
insert into tab2(important_data, parent_id) values('four child two', 2)\\
insert into tab2(important_data, parent_id) values('one child three', 3)\\
insert into tab2(important_data, parent_id) values('two child three', 3)\\
insert into tab2(important_data, parent_id) values('three child three', 3)\\
insert into tab2(important_data, parent_id) values('four child three', 3)\\

select id2, important_data, parent_id, (@num:=if(@parent_id=parent_id, @num+1, if(@parent_id:=parent_id, 1, 1))) row_number
from tab2
cross join (select @num:=0, @parent_id:=null) c
order by parent_id, id2, important_data\\


select tab1.id, tab1.name, data1.id2, data1.parent_id, data1.important_data as data_one
    , data2.important_data as data_two, data3.important_data as data_three
from tab1
left outer join
    (
    select id2, important_data, parent_id, (@num:=if(@parent_id=parent_id, @num+1, if(@parent_id:=parent_id, 1, 1))) row_number
    from tab2
    cross join (select @num:=0, @parent_id:=null) c
    order by parent_id, id2, important_data
    ) as data1 on data1.parent_id = tab1.id and data1.row_number=1
left outer join
    (
    select id2, important_data, parent_id, (@num:=if(@parent_id=parent_id, @num+1, if(@parent_id:=parent_id, 1, 1))) row_number
    from tab2
    cross join (select @num:=0, @parent_id:=null) c
    order by parent_id, id2, important_data
    ) as data2 on data2.parent_id = tab1.id and data2.row_number=2
left outer join
    (
    select id2, important_data, parent_id, (@num:=if(@parent_id=parent_id, @num+1, if(@parent_id:=parent_id, 1, 1))) row_number
    from tab2
    cross join (select @num:=0, @parent_id:=null) c
    order by parent_id, id2, important_data
    ) as data3 on data3.parent_id = tab1.id and data3.row_number=3\\