Self-join a single table that includes 'parent-child' relation.

Posted on 2007-10-10
Last Modified: 2010-05-18
id, name, parent_id, type, hobbies

1, Tim, <null>, Parent, Fishing and hiking
2, John, 1, Child, Reading and biking
3, Jane, 1, Child, Poker and cooking

From the query result I need to construct this:
{Tim}'s hobbies are {Fishing and hiking}, he's a {Parent}
       {John}, {Tim}'s {Child} likes {Reading and biking}
       {Jane,} {Tim}'s {Child} likes {Poker and cooking}
Continuing parent/child lists...

The kicker is I have no logic available to me except via the SQL (MSSQL, not sure what version). I can issue the statement and then display the results in a single instance of formatted list code. I expect I'll create a stylesheet to handle the indenting of the records based on type (class="Parent", class="Child", etc.). Not sure about other things like getting records into the right sort order to format the list. There are no children that are also parents (thank goodness).

Can I get this to work? Let me know if you need more info.
Question by:timprotech
    LVL 18

    Accepted Solution

    Here you go:

    create table #Y (id int, name varchar(10), relid int null, rel varchar(10), hobby varchar(100))

    insert into #Y values (1, 'Tim', null, 'Parent', 'Fishing and hiking')
    insert into #Y values (2, 'John', 1, 'Child', 'Reading and biking')
    insert into #Y values (3, 'Jane', 1, 'Child', 'Poker and cooking')

    select, isnull(, ''), Y1.rel, Y1.hobby
    from   #Y Y1
    left   outer join #Y Y2
    on     Y1.relid =

    -->> result:
    name                  rel        hobby
    ---------- ---------- ---------- --------------------
    Tim                   Parent     Fishing and hiking
    John       Tim        Child      Reading and biking
    Jane       Tim        Child      Poker and cooking

    Hope this helps ...

    LVL 1

    Expert Comment

    I believe this should generate what you want

    Select ids, names, hobbie from(
      (select as ids, as name, a.hobbies as hobbie, as ord
      from table as a where a.type="parent")
      (select as ids, as names, b. hobbies as hobbie, as ord
      from table as a
       join table as b    on
    order by ord
    LVL 32

    Expert Comment

    If the assumption is that you only have two levels - Parent and Child - then these both should provide you with the data you need.  However, you can also format your output thusly:

        CASE WHEN parent_id IS NULL
            THEN Name + CASE WHEN Right(Name, 1) = 's' THEN '''' ELSE '''s' END + ' hobbies are ' + hobbies + ', he's a Parent'
            ELSE '        ' + Name + ', ' + (SELECT s2.Name + CASE WHEN Right(s2.Name, 1) = 's' THEN '''' ELSE '''s' END FROM MyTable s2 WHERE = s.parent_id) + ' Child likes ' + hobbies
    FROM MyTable s
    ORDER BY isNull(parent_id, id), id

    Author Closing Comment

    Nicely presented: input, processing steps, then output. That's not always easy to do.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now