1

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:

http://prntscr.com/3kui69

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.

Nino
  • 129
  • 1
  • 2
  • 12
  • Similar question with possible solutions: http://stackoverflow.com/questions/7604893/sql-select-row-from-table-where-id-maxid – Rivnat May 20 '14 at 05:31

1 Answers1

2

Please 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

Negev Nomad
  • 123
  • 3