Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2015
    Posts
    4

    Answered: recursive calling

    Code:
    table "geo"
    
    (id) mom   geo
    (1)   1    Earth
    (2)   1    Asia
    (3)   2    China
    (4)   2    Japan
    (5)   1    Europe
    (6)   5    Germany
    (7)   6    Berlin
    (8)   3    Peking
    I have a table like the above.

    With id=2(Asia), I like to produce my target result2 below.
    Code:
    target result2
    (2) Asia
    (3) China
    (4) Japan
    (8) Peking
    With id=3(China), I like to produce my target result3 below.
    Code:
    target result3
    (3) China
    (8) Peking
    With id=4(Japan), I like to produce my target result4 below.
    Code:
    target result4
    (4) Japan
    With id=5(Europe), I like to produce my target result5 below.
    Code:
    target result5
    (5) Europe
    (6) Germany
    (7) Berlin
    With id=6(Germany), I like to produce my target result6 below.
    Code:
    target result6
    (6) Germany
    (7) Berlin
    How can I get my target results above?

    The following query is one of my trials which doesn't work correctly
    Code:
    trial 
    select geo1.id, geoName, my1.mom from geo AS geo1
    left join my AS my1 on my1.id=geo1.id
    where geo1.id=$id
    union
    select geo2.id, geoName, my2.mom from geo1 AS geo2
    left join my AS my2 on my2.id=geo2.id
    where geo2.id=$id
    
    result
    Asia 
    China 
    
    target result
    (2) Asia
    (3) China
    (4) Japan
    (8) Peking

  2. Best Answer
    Posted by healdem

    "
    Code:
    select a.item as planet, b.item as continent, c.item as country, d.item as region, e.item as city, f.item as district, g.item as locale
    from joonstar as a 
    left outer join joonstar as b on b.parent_id = a.ID
    left outer join joonstar as c on c.parent_id = b.ID
    left outer join joonstar as d on d.parent_id = c.ID
    left outer join joonstar as e on e.parent_id = d.ID
    left outer join joonstar as f on f.parent_id = e.ID
    left outer join joonstar as g on g.parent_id = f.ID
    where a.parent_id is null
    ;
    should get you somewhere near to where you want to be.

    in this example Ive used a table called joonstar
    columns are ID, integer
    items, varchar 50
    parent_id, integer
    although I haven't defined any priamry keys, indicies or relations. parent ID is nullable (if its null then its a top level rows, hence why
    Code:
    where a.parent_id is null
    . that forces a to be the top of the tree

    on some test data this generates:-
    Code:
    Earth	Asia	China	Gansu			
    Earth	Asia	China	Guangdong			
    Earth	Asia	China	Qinghai			
    Earth	Asia	China	Sichuan			
    Earth	Asia	China	Tibet			
    Earth	Asia	China	Xinjiang			
    Earth	Asia	China	Yunnan			
    Earth	Asia	Japan	Chubu			
    Earth	Asia	Japan	Chugoku			
    Earth	Asia	Japan	Hokkaido	Sapporo		
    Earth	Asia	Japan	Kanto	Chiba		
    Earth	Asia	Japan	Kanto	Kawasaki		
    Earth	Asia	Japan	Kanto	Saitama		
    Earth	Asia	Japan	Kanto	Tokyo	Adachi	
    Earth	Asia	Japan	Kanto	Tokyo	Akasaka	
    Earth	Asia	Japan	Kanto	Tokyo	Akihabara	
    Earth	Asia	Japan	Kanto	Tokyo	Arakawa	
    Earth	Asia	Japan	Kanto	Tokyo	Asakusa	
    Earth	Asia	Japan	Kanto	Tokyo	Bunkyo	
    Earth	Asia	Japan	Kanto	Tokyo	Ebisu	
    Earth	Asia	Japan	Kanto	Tokyo	Edogawa	
    Earth	Asia	Japan	Kanto	Tokyo	Ginza	
    Earth	Asia	Japan	Kanto	Tokyo	Harajuku	
    Earth	Asia	Japan	Kanto	Tokyo	Itabashi	
    Earth	Asia	Japan	Kanto	Tokyo	Katsushuka	
    Earth	Asia	Japan	Kanto	Tokyo	Kita	
    Earth	Asia	Japan	Kanto	Tokyo	Koto	
    Earth	Asia	Japan	Kanto	Tokyo	Meguro	
    Earth	Asia	Japan	Kanto	Tokyo	Nakano	
    Earth	Asia	Japan	Kanto	Tokyo	Nerima	
    Earth	Asia	Japan	Kanto	Tokyo	Odaiba	
    Earth	Asia	Japan	Kanto	Tokyo	Roppongi	
    Earth	Asia	Japan	Kanto	Tokyo	Setagaya	
    Earth	Asia	Japan	Kanto	Tokyo	Shinagawa	
    Earth	Asia	Japan	Kanto	Tokyo	Shinjuku	
    Earth	Asia	Japan	Kanto	Tokyo	Shiodome	
    Earth	Asia	Japan	Kanto	Tokyo	Suginami	
    Earth	Asia	Japan	Kanto	Tokyo	Sumida	
    Earth	Asia	Japan	Kanto	Tokyo	Toshima	
    Earth	Asia	Japan	Kanto	Yokohama		
    Earth	Asia	Japan	Kinki			
    Earth	Asia	Japan	Kyushu			
    Earth	Asia	Japan	Okinawa			
    Earth	Asia	Japan	Tohoku			
    Earth	Asia	Taiwan
    you can join the table as often as you need
    the real difficulty with this approach is if you miss a layer. eg smaller cities may not have districts or locales.
    so if you need a more rigid structure then define a rigid structure (possibly using another table(s) and or defining the attribute type as part of the row defintion and that probably means on what level a row should appear"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    after you've taken a look at the above and have attempted to correct your SQL come back with additional questions. Pay particular attention to your title for this thread, recursion.
    Dave

  5. #4
    Join Date
    Jun 2015
    Posts
    4

    recursion calling

    Quote Originally Posted by dav1mo View Post
    Pay particular attention to your title for this thread, recursion.
    Thank you for your pointing out. I am learning not only SQL but also English from you.

    Quote Originally Posted by dav1mo View Post
    after you've taken a look at the above and have attempted to correct your SQL come back with additional questions.
    Thank you for your thoughtful comment and kindness.
    The trial SQL below is my correcting as you comment.
    Code:
     trial SQL
    select root.geoName  as root_name
         , down1.geoName as down1_name
         , down2.geoName as down2_name
     
       from geo as root
     left outer
       join geo as down1
         on down1.mom = root.id
     left outer
       join geo as down2
         on down2.mom = down1.id
     where root.id=2
     order 
         by root.id
    
    trial result
    Asia China Peking 
    Asia Japan
    The target result below is what I want.
    Code:
    taget result  
    Asia
    China
    Peking
    Japan
    The target result2 below is, I think, better than target result above.
    Code:
    target result2
    1 Asia   
    2 China
    3 Peking
    2 Japan
    
    /* 1 is the very 1st tree from ID=2(Asia).
    2 is the 2nd tree from ID=2(Asia).
    3 is the 3rd tree from ID=2(Asia).
    The target result3 below is, I think, the best than target results above.
    Code:
    target result3
    Asia   
    Asia > China
    Asia > China > Peking
    Asia > Japan
    How can I get the target result1, result2 or result3?

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    select a.item as planet, b.item as continent, c.item as country, d.item as region, e.item as city, f.item as district, g.item as locale
    from joonstar as a 
    left outer join joonstar as b on b.parent_id = a.ID
    left outer join joonstar as c on c.parent_id = b.ID
    left outer join joonstar as d on d.parent_id = c.ID
    left outer join joonstar as e on e.parent_id = d.ID
    left outer join joonstar as f on f.parent_id = e.ID
    left outer join joonstar as g on g.parent_id = f.ID
    where a.parent_id is null
    ;
    should get you somewhere near to where you want to be.

    in this example Ive used a table called joonstar
    columns are ID, integer
    items, varchar 50
    parent_id, integer
    although I haven't defined any priamry keys, indicies or relations. parent ID is nullable (if its null then its a top level rows, hence why
    Code:
    where a.parent_id is null
    . that forces a to be the top of the tree

    on some test data this generates:-
    Code:
    Earth	Asia	China	Gansu			
    Earth	Asia	China	Guangdong			
    Earth	Asia	China	Qinghai			
    Earth	Asia	China	Sichuan			
    Earth	Asia	China	Tibet			
    Earth	Asia	China	Xinjiang			
    Earth	Asia	China	Yunnan			
    Earth	Asia	Japan	Chubu			
    Earth	Asia	Japan	Chugoku			
    Earth	Asia	Japan	Hokkaido	Sapporo		
    Earth	Asia	Japan	Kanto	Chiba		
    Earth	Asia	Japan	Kanto	Kawasaki		
    Earth	Asia	Japan	Kanto	Saitama		
    Earth	Asia	Japan	Kanto	Tokyo	Adachi	
    Earth	Asia	Japan	Kanto	Tokyo	Akasaka	
    Earth	Asia	Japan	Kanto	Tokyo	Akihabara	
    Earth	Asia	Japan	Kanto	Tokyo	Arakawa	
    Earth	Asia	Japan	Kanto	Tokyo	Asakusa	
    Earth	Asia	Japan	Kanto	Tokyo	Bunkyo	
    Earth	Asia	Japan	Kanto	Tokyo	Ebisu	
    Earth	Asia	Japan	Kanto	Tokyo	Edogawa	
    Earth	Asia	Japan	Kanto	Tokyo	Ginza	
    Earth	Asia	Japan	Kanto	Tokyo	Harajuku	
    Earth	Asia	Japan	Kanto	Tokyo	Itabashi	
    Earth	Asia	Japan	Kanto	Tokyo	Katsushuka	
    Earth	Asia	Japan	Kanto	Tokyo	Kita	
    Earth	Asia	Japan	Kanto	Tokyo	Koto	
    Earth	Asia	Japan	Kanto	Tokyo	Meguro	
    Earth	Asia	Japan	Kanto	Tokyo	Nakano	
    Earth	Asia	Japan	Kanto	Tokyo	Nerima	
    Earth	Asia	Japan	Kanto	Tokyo	Odaiba	
    Earth	Asia	Japan	Kanto	Tokyo	Roppongi	
    Earth	Asia	Japan	Kanto	Tokyo	Setagaya	
    Earth	Asia	Japan	Kanto	Tokyo	Shinagawa	
    Earth	Asia	Japan	Kanto	Tokyo	Shinjuku	
    Earth	Asia	Japan	Kanto	Tokyo	Shiodome	
    Earth	Asia	Japan	Kanto	Tokyo	Suginami	
    Earth	Asia	Japan	Kanto	Tokyo	Sumida	
    Earth	Asia	Japan	Kanto	Tokyo	Toshima	
    Earth	Asia	Japan	Kanto	Yokohama		
    Earth	Asia	Japan	Kinki			
    Earth	Asia	Japan	Kyushu			
    Earth	Asia	Japan	Okinawa			
    Earth	Asia	Japan	Tohoku			
    Earth	Asia	Taiwan
    you can join the table as often as you need
    the real difficulty with this approach is if you miss a layer. eg smaller cities may not have districts or locales.
    so if you need a more rigid structure then define a rigid structure (possibly using another table(s) and or defining the attribute type as part of the row defintion and that probably means on what level a row should appear
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Jun 2015
    Posts
    4
    Code:
    Earth	Asia	China	Gansu			
    Earth	Asia	China	Guangdong			
    Earth	Asia	China	Qinghai			
    Earth	Asia	China	Sichuan			
    Earth	Asia	China	Tibet			
    Earth	Asia	China	Xinjiang			
    Earth	Asia	China	Yunnan			
    Earth	Asia	Japan	Chubu			
    Earth	Asia	Japan	Chugoku			
    Earth	Asia	Japan	Hokkaido	Sapporo		
    Earth	Asia	Japan	Kanto	Chiba		
    Earth	Asia	Japan	Kanto	Kawasaki		
    Earth	Asia	Japan	Kanto	Saitama		
    Earth	Asia	Japan	Kanto	Tokyo	Adachi	
    Earth	Asia	Japan	Kanto	Tokyo	Akasaka	
    Earth	Asia	Japan	Kanto	Tokyo	Akihabara	
    Earth	Asia	Japan	Kanto	Tokyo	Arakawa	
    Earth	Asia	Japan	Kanto	Tokyo	Asakusa	
    Earth	Asia	Japan	Kanto	Tokyo	Bunkyo	
    Earth	Asia	Japan	Kanto	Tokyo	Ebisu	
    Earth	Asia	Japan	Kanto	Tokyo	Edogawa	
    Earth	Asia	Japan	Kanto	Tokyo	Ginza	
    Earth	Asia	Japan	Kanto	Tokyo	Harajuku	
    Earth	Asia	Japan	Kanto	Tokyo	Itabashi	
    Earth	Asia	Japan	Kanto	Tokyo	Katsushuka	
    Earth	Asia	Japan	Kanto	Tokyo	Kita	
    Earth	Asia	Japan	Kanto	Tokyo	Koto	
    Earth	Asia	Japan	Kanto	Tokyo	Meguro	
    Earth	Asia	Japan	Kanto	Tokyo	Nakano	
    Earth	Asia	Japan	Kanto	Tokyo	Nerima	
    Earth	Asia	Japan	Kanto	Tokyo	Odaiba	
    Earth	Asia	Japan	Kanto	Tokyo	Roppongi	
    Earth	Asia	Japan	Kanto	Tokyo	Setagaya	
    Earth	Asia	Japan	Kanto	Tokyo	Shinagawa	
    Earth	Asia	Japan	Kanto	Tokyo	Shinjuku	
    Earth	Asia	Japan	Kanto	Tokyo	Shiodome	
    Earth	Asia	Japan	Kanto	Tokyo	Suginami	
    Earth	Asia	Japan	Kanto	Tokyo	Sumida	
    Earth	Asia	Japan	Kanto	Tokyo	Toshima	
    Earth	Asia	Japan	Kanto	Yokohama		
    Earth	Asia	Japan	Kinki			
    Earth	Asia	Japan	Kyushu			
    Earth	Asia	Japan	Okinawa			
    Earth	Asia	Japan	Tohoku			
    Earth	Asia	Taiwan
    Your code produces the result above. It's good and teach me about recursion calling, but it's not my target result what I want exactly.
    The result above has 45 records, but I want my target result below which has 51 records.

    Code:
    Earth
    Asia
    China	
    Gansu			
    Guangdong			
    Qinghai			
    Sichuan			
    Tibet			
    Xinjiang			
    Yunnan			
    Japan
    Chubu			
    Chugoku			
    Sapporo		
    Kanto	
    Chiba			
    Kawasaki		
    Saitama		
    Tokyo	
    Adachi	
    Akasaka	
    Akihabara	
    Arakawa	
    Asakusa	
    Bunkyo	
    Ebisu	
    Edogawa	
    Ginza	
    Harajuku	
    Itabashi	
    Katsushuka	
    Kita	
    Koto	
    Meguro	
    Nakano	
    Nerima	
    Odaiba	
    Roppongi	
    Setagaya	
    Shinagawa	
    Shinjuku	
    Shiodome	
    Suginami	
    Sumida	
    Toshima	
    Yokohama		
    Kinki			
    Kyushu			
    Okinawa			
    Tohoku			
    Taiwan
    For get the target result above, I tried left join like the below, but failed.
    Code:
    select root.geoName  as root_name
         , down1.geoName as down1_name
         , down2.geoName as down2_name
       from geo as root
     left
       join geo as down1
         on down1.mom = root.id
     left
       join geo as down2
         on down2.mom = down1.id
    where root.id=2
    How can I get my target result below with the table "geo" and "id=2"?

    Code:
    table "geo"
    
    (id) mom   geo
    (1)   1    Earth
    (2)   1    Asia
    (3)   2    China
    (4)   2    Japan
    (5)   1    Europe
    (6)   5    Germany
    (7)   6    Berlin
    (8)   3    Peking
    
    target result
    
    Asia
    China
    Peking
    Japan
    Last edited by joonstar; 06-04-15 at 05:43.

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in poist #4 you seemed to infer that the above would be:_
    he target result3 below is, I think, the best than target results above.
    if that no longer applies then the only way I can see of acheiveing your desired resultset is either
    through a stored procedure
    OF handler this inside a front end

    im not convinced that what you want is doable with SQL queries alone
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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