SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Calling direct ancesters in recursive

    Code:
    data in myTable
    
    (id) mom geo
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America
    (3) 0 Europe
    (4) 2 L.A.
    (5) 2 New York
    (6) 5 Manhatan
    (7) 3 France
    (8) 7 Paris
    I have data in myTable like the above.

    When myVariable is (2) ,
    the query below produces all kids of the record (2).
    Code:
    query
    
    select t1.id, t1.mom, t1.geo
      from myTable t1   
     
      join myTable t2
      on t1.mom = t2.id 
     
      where  t1.mom = #variables.V# or
                t2.mom = #variables.V# 
            order by id
    
    result
    
    (4) 2 L.A.
    (5) 2 New York
    (6) 5 Manhatan
    Instead of the kids, I like to produce ancesters of the record(2) and the record(2) itself.

    The following trial code seems not to work as I want.
    Code:
    trial code
    
    select t1.id, t1.mom, t1.geo
      from myTable t1   
     
      join myTable t2
      on t1.id = t2.mom 
     
      where  t1.id = #variables.V# or
                t2.id = #variables.V# 
            order by id
    
    result of the trial code
    
    (1) 0 North America
    (2) 1 America
    (2) 1 America
    
     my target result
    
    (0) 0 earth
    (1) 0 North America
    (2) 1 America

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    1) You aren't getting earth, because earth is not a first grade ancestor, but second (so you'd have to add another join to the query
    2) You're getting America twice, because there are two rows in t1 with mom = 2. Use SELECT DISTINCT to get rid of the duplicates

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    1) You aren't getting earth, because earth is not a first grade ancestor, but second (so you'd have to add another join to the query
    2) You're getting America twice, because there are two rows in t1 with mom = 2. Use SELECT DISTINCT to get rid of the duplicates
    Thank you, guido, for the hint. I got the result what I want now.


Bookmarks

Posting Permissions

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