SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  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)

    self-joined on two columns

    Code:
    data in myTable2
    
    (id) cate  name        country
    (1)   0    Datcher     England
    (2)   0    Chingiskhan Mongol
    (3)   0    Clington    America
    (4)   0    Caesar      Rome
    (5)   0    Bush        America
    (6)   1    3           5
    (7)   0    Blair       England
    (8)   0    Batu        Mongol
    (9)   2    2           8
    (10)  0    Koismi      Japan
    (11)  0    Nero        Rome
    (12)  1    1           7
    
    //cate=0 real data
    
    cate=1,2 relational data
    cate=1 (new country) cate=2(old country)
    
    number in name : former ruler  
    number in country : later ruler
    The following would-be code doesn't work correctly, but I hope it will show what I want.

    Code:
    would-be code
    
    select 
           t1.id,
           t1.name,
           t1.country
           from myTable2 t1
    left outer join myTable2 t2
    on t1.id = t2.name
    left outer join myTable2 t3
    on t1.id = t3.country
    
    where 
       t1.top not regexp '^[[:digit:]]+$' 
      and
      ( t2.cate<>2 or t3.cate<>2 )  
    order by id
    
    target result
    
    (1)      Datcher     England
    (3)      Clington    America
    (4)      Caesar      Rome
    (5)      Bush        America
    (7)      Blair       England
    (10)     Koismi      Japan
    (11)     Nero        Rome
    
    //produces real data only without old country
    Please notice Rome isn't relationed yet.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this is just like your previous question, except:
    1) you don't need the regexp because you can just test for t1.cate = 0
    2) you need to join the table with an extra condition (...and t2.cate = 2)
    3) you need to join the table twice to check both the name column and the country column

    if you want to see the answer, highlight the grey boxes below.
    select t1.id
    , t1.name
    , t1.country
    from myTable t1
    left outer
    join myTable t2
    on t2.name = t1.id
    and t2.cate = 2
    left outer
    join myTable t3
    on t3.country = t1.id
    and t2.cate = 2
    where t1.cate = 0
    and (t2.id is null or t3.id is null)


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
  •