Results 1 to 2 of 2

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

  1. #1
    Join Date
    May 2014
    Posts
    1

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

    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.

  2. #2
    Join Date
    May 2015
    Posts
    19
    Code:
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •