?
Solved

Help with SQL Parent/Child Relationship

Posted on 2009-12-25
5
Medium Priority
?
295 Views
Last Modified: 2012-05-08
Hello Experts...
I am in over my head. I have a single table that has a parent/child relationship stored in it.
I don't know how to query this to get the strings in the Parent/Child Order.

What I need is to return multiple rows with the parent/child on the same row:
IE:
Tools , Bit & Nut Set
Tools , Carpentry Tools

If for some reason if I have a child of a child display that as well.
Tools . Bits & Nut Set , Metric
 
Thanks for your help...
Category.jpg
0
Comment
Question by:fullbugg
5 Comments
 
LVL 83

Expert Comment

by:leakim971
ID: 26123422
Hello fullbugg,

You can use something like that :

(replace "yourTable" by your true table name)
SELECT * FROM yourTable Parent, youTable Child WHERE Child.IDParentCategory = Parent.IDCategory

Open in new window

0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 26123423
use this (if you have max level 3)

select p1.*, p2.Description as Description2, p3.Description as Description3,
p1.Description + (
case when p2.description is not null then ', ' + p2.description +
(case when p3.description is not null then ', ' + p3.description else '' end)
else '' end) as FullDescription
from parts p1 left join parts p2 on p1.idcategory=p2.idparentcategory
left join parts p3 on p2.idcategory=p3.idparentcategory

0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 26123440
Haven't tested this, but see if it gets you close to what you want.

~bp
SELECT t1.DESCRIPTION & 
       CASE WHEN (t2.DESCRIPTION IS NOT NULL) THEN ', ' ELSE "" END & 
       t2.DESCRIPTION & 
       CASE WHEN (t3.DESCRIPTION IS NOT NULL) THEN ', ' ELSE "" END & 
       t3.DESCRIPTION
  FROM mytable t1
       INNER JOIN mytable t2
         ON t2.idparentcategory = t1.idcategory
       INNER JOIN mytable t3
         ON t3.idparentcategory = t2.idcategory
 WHERE t1.idparentcategory = 1
   AND t1.idcategory > 1;

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26123443
also add below to 26123423

where p1.idparentcategory = 1

to get only root items
0
 

Author Closing Comment

by:fullbugg
ID: 31669990
Thanks so much... This is perfect.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

807 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