SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Parents and children

    With a table like this:

    Code:
    id| name    | parent
    ====================
    1 | parent1 | null
    2 | parent2 | null
    3 | child1  | 1
    4 | child2  | 1
    5 | child3  | 2
    I want to get the following result:

    parent1
    - child1
    - child2
    parent2
    - child3

    I'm currently doing a LEFT JOIN on parent = id. Is there a better way to achieve what I want?

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it's not clear what you want, but yes, a left join is always a good idea <grin>

    you could be looking for parents with no children

    or children with their parents to the third generation (if any)

    or parents with grandchildren

    the specific nature of the left join depends on what you're after

    as for the structure, it's fine, a classic example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. There will only be parents and children (i.e. only two levels), so how does this look:

    Code:
    SELECT cat.id, cat.name
    FROM table AS cat
    LEFT JOIN table AS chil ON chil.parent = cat.id
    ?

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's good, as far as it goes

    here's what it produces --

    1 parent1
    1 parent1
    2 parent2

    can you see why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It will return a parent each time it's used, i.e. there are two children with a parent of parent1, so parent1 will be returned twice ?

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    correct

    if this is going too slow, let me know


    without changing anything else, make this your SELECT clause --
    Code:
    SELECT cat.id, cat.name, chil.id, chil.name
    that is the result set that you want to bring into your program or script

    to get the results displayed in nested output format, i.e. header and detail lines, please see http://searchdatabase.techtarget.com...285649,00.html (free registration may be required, but go ahead, it's worth it )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •