SQL QUERY - How to extract last child of a table?

[nino59]

Hi,

I have a MYSQL table of groups of people organised by country, region, sub region and city.

When visitor join a group, he select a city and we automatically add him in the parents groups "sub-region", "region, "country".

For example: John selected London. So he will be added in groups London, Greater London, England, UK.
We get a parent-child table like this:

table.png

I need to extract all the rows for the city groups. How to recognise a city groups? It is the only rows where its ID is not in id_parent field of other rows.
Yes! City groups rows can't be parents of other groups. So we can't find city groups id in id_parent fields.

Now that we know his, how can I extract the city groups rows with SQL language? It is too complicate for me.

Thanks in advance.

[ted]

create table tbl (id integer, id_parent integer, name varchar(20));

insert into tbl values (1, 0, 'UK');
insert into tbl values (2, 1, 'England');
insert into tbl values (3, 2, 'Creater London');
insert into tbl values (4, 3, 'London');

select * from tbl
where id not in (select id_parent from tbl);

Output will be: 4	    3         London

I hope it will help you.