SQL QUERY - How to retrieve the last child of a table?

Question

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

When a visitor joins a group, he chooses a city, and we automatically add it to the parent groups "subregion", "region", "country".

For example: John chose London. Therefore, it will be added to the groups London, Greater London, England, UK. We get the parent-child table as follows:

http://prntscr.com/3kui69

I need to extract all rows for city groups. How to recognize city groups? These are the only lines where its identifier is not in the id_parent field of the other lines. Yes! Rows of city groups cannot be parents of other groups. Thus, we cannot find the identifiers of city groups in the id_parent fields.

Now that we know it, how can I extract strings of city groups with SQL? This is too hard for me.

Thanks in advance.

Answer 1

Try using this query:

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

Here is an example: http://sqlfiddle.com/#!2/7de26/1