Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3164
  • Last Modified:

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

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.
1 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 Y1.name, isnull(Y2.name, ''), Y1.rel, Y1.hobby
from   #Y Y1
left   outer join #Y Y2
on     Y1.relid = Y2.id

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

Hope this helps ...

I believe this should generate what you want

Select ids, names, hobbie from(
  (select a.id as ids, a.name as name, a.hobbies as hobbie, a.id as ord
  from table as a where a.type="parent")
  (select b.id as ids, b.name as names, b. hobbies as hobbie, a.id as ord
  from table as a
   join table as b    on   a.id=b.parent_id))
order by ord
Brendt HessSenior DBACommented:
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 s2.id = s.parent_id) + ' Child likes ' + hobbies
FROM MyTable s
ORDER BY isNull(parent_id, id), id
timprotechAuthor Commented:
Nicely presented: input, processing steps, then output. That's not always easy to do.

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now