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

Table:
id, name, parent_id, type, hobbies

Example:
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.
timprotechAsked:
Who is Participating?
 
YveauCommented:
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 ...

0
 
elec1celeCommented:
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")
  union
  (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
0
 
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:

SELECT
    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
    END
FROM MyTable s
ORDER BY isNull(parent_id, id), id
0
 
timprotechAuthor Commented:
Nicely presented: input, processing steps, then output. That's not always easy to do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.