SQL QUERY - How to extract last child of a table?
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:
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.
I hope it will help you.
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
Tags for this Thread