SitePoint Sponsor

User Tag List

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

    1st generation people only

    Code:
    myTable1
    (id) name
    (1) Tom
    (2) Jane
    (3) Chris
    (4) Judy
    (5) Mary
    (6) Jack
    (7) James
    (8) Bill
    
    myTable
    (pom) kid
    (1) 2
    (3) 1
    (3) 4
    (5) 8
    I have 2 tables like the above.
    I like to say the tables says like the below.

    Jane is a kid of Tom.
    Tom and Judy are kids of Chris.
    Bill is a kid of Mary.

    Chris is the pom(papa or mom) of Tom and Judy.
    Mary is the pom of Bill.
    Tom is the pom of Jane.

    I like to produce who are poms.
    The code below produces the result below.
    Code:
    code
    select name
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    where
    myTable.pom is not null
    group by pom
    order by name
    
    result
    Chris
    Mary
    Tom
    Tom is the 2nd generation while Chris and Mary are the 1st generation,
    because Tom is a kid of Chris.
    I like to remove Tom for producing the 1st generation people only.

    The following is my target result with myTable1 and myTable above.
    Code:
    target result
    
    Chris
    Mary
    How can I get my target result above?


    The following is one of my trials, but it causes an error.
    Code:
    select name
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    left join myTable on myTable1.id=myTable.kid
    where
    myTable.pom is not null
    and
    myTable.kid is null
    group by pom
    order by name

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You're almost there, but since you're linking the same table twice, you must give them aliasses, otherwise MySQL can't know when you're using what "instance" of the table.
    Code:
    select DISTINCT name
    from myTable1
    left join myTable AS a
    on myTable1.id = a.pom
    left join myTable AS b
    on myTable1.id = b.kid
    where a.pom is not null
    and   b.kid is null
    order by name
    Last edited by guido2004; Jun 11, 2012 at 06:33. Reason: corrected error in query

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    joon obviously forgot all the many, many threads where i already taught him this stuff

    all i can add to this thread is my best wishes to guido and i hope you have a lot of patience, buddy

    by the way, a.myTable.pom and b.myTable.kid are malformed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    by the way, a.myTable.pom and b.myTable.kid are malformed
    Forgot to delete the myTable part
    I've corrected the query in my previous post.

  5. #5
    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 r937 View Post
    by the way, a.myTable.pom and b.myTable.kid are malformed
    Do you mean myTable which has 2 columns "pom and kid" is badly designed?

  6. #6
    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)
    As I see carefully the posts, I found it means a kind of typo instead of telling a badly designed table.
    Thank you, guido2004 and rudy.
    I hope you enjoy your patience like I do.


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
  •