February 25, 2009 at 9:10 am
This is the scenario:
I have a table called compliants where we store a
compliant (parent records) and issues (child records) for each complaint
eg:
compliantid Title status category type childid parentid
1011 X openhigh 1 011 null
1012 Y openmedium 1 012 null
1022 A closed high 1 022 null
1024 aa open high 2 null 011
1025 bb closed high 2 null 011
1026 cc open high 2 null 011
1027 dd open high 2 null 011
1028 mm closed high 2 null 022
NOTE: type field tells that whether the records is parent or child, we have a reference table called Type :
Id Desc
1 compliantParent
2 childIssue
Now what I am trying to do is to get the compliantid, title, status, category for each parent record (compliant) along with all the title and the status of all its child records (issues).
something like:
1011 X openhigh aa open
bb closed
cc open
dd open
1022 A closed high mm closed
1012 Y openmedium null null
February 25, 2009 at 9:20 am
dua_anshu (2/25/2009)
This is the scenario:I have a table called compliants where we store a
compliant (parent records) and issues (child records) for each complaint
eg:
compliantid Title status category type childid parentid
1011 X openhigh 1 011 null
1012 Y openmedium 1 012 null
1022 A closed high 1 022 null
1024 aa open high 2 null 011
1025 bb closed high 2 null 011
1026 cc open high 2 null 011
1027 dd open high 2 null 011
1028 mm closed high 2 null 022
NOTE: type field tells that whether the records is parent or child, we have a reference table called Type :
Id Desc
1 compliantParent
2 childIssue
Now what I am trying to do is to get the compliantid, title, status, category for each parent record (compliant) along with all the title and the status of all its child records (issues).
something like:
1011 X openhigh aa open
bb closed
cc open
dd open
1022 A closed high mm closed
1012 Y openmedium null null
Please read the first article I have linked below in my signature block regarding asking for help. Follow the guidelines in the article to post the DDL and sample data. You will get much better answers to your requests for help. I'd like to help, but I don't have time to create your table or modify your sample data for importing.
One thing I would ask, why are you mixing parent and child records in the same table? These should be separated into their own respective tables and that would make joining them together easier.
February 25, 2009 at 9:48 am
I recommend taking a look at the section in Books Online on Common Table Expressions. There's a very good example in there of resolving this type of hierarchy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 1:15 pm
sorry for the format.
Here are the sql scripts for the question I have asked. Hope
this will make more sense to the readers.
-- Table scheme
-- [Type] field tells whether the record in the CompliantTable is a
-- Parent record or child record
-- Type = 1 means Parent record
-- Type = 2 means Child record
-- Parent and child are linked using childId and parentId fields
declare @CompliantTable Table
(
CompliantID int
,Title text
,Status varchar(10)
,category varchar(10)
,[type] int
,childId int
,parentId int
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1011
,'X'
,'open'
,'high'
,1
,011
,null
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1012
,'Y'
,'open'
,'medium'
,1
,012
,null
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1022
,'A'
,'closed'
,'high'
,1
,022
,null
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1024
,'aa'
,'open'
,'high'
,2
,null
,011
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1025
,'bb'
,'closed'
,'high'
,2
,null
,011
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1026
,'cc'
,'open'
,'high'
,2
,null
,011
)
insert into @CompliantTable
(
CompliantID
,Title
,Status
,category
,[type]
,childId
,parentId
)
values
(
1027
,'mm'
,'closed'
,'high'
,2
,null
,022
)
select * from @CompliantTable
--Result I am trying to get
declare @ResultTable Table
(
parentcompliantId int
,parentTitle text
,parentstatus varchar(10)
,parentcategory varchar(10)
,childTitle text
,childStatus varchar(10)
)
insert into @ResultTable
(
parentcompliantId
,parentTitle
,parentstatus
,parentcategory
,childTitle
,childStatus
)
values
(
1011
,'X'
,'open'
,'high'
,'aa'
,'open'
)
insert into @ResultTable
(
parentcompliantId
,parentTitle
,parentstatus
,parentcategory
,childTitle
,childStatus
)
values
(
null
,null
,null
,null
,'bb'
,'closed'
)
insert into @ResultTable
(
parentcompliantId
,parentTitle
,parentstatus
,parentcategory
,childTitle
,childStatus
)
values
(
null
,null
,null
,null
,'cc'
,'open'
)
insert into @ResultTable
(
parentcompliantId
,parentTitle
,parentstatus
,parentcategory
,childTitle
,childStatus
)
values
(
1022
,'A'
,'closed'
,'high'
,'mm'
,'closed'
)
select * from @ResultTable
Lynn Pettis (2/25/2009)
dua_anshu (2/25/2009)
This is the scenario:I have a table called compliants where we store a
compliant (parent records) and issues (child records) for each complaint
eg:
compliantid Title status category type childid parentid
1011 X openhigh 1 011 null
1012 Y openmedium 1 012 null
1022 A closed high 1 022 null
1024 aa open high 2 null 011
1025 bb closed high 2 null 011
1026 cc open high 2 null 011
1027 dd open high 2 null 011
1028 mm closed high 2 null 022
NOTE: type field tells that whether the records is parent or child, we have a reference table called Type :
Id Desc
1 compliantParent
2 childIssue
Now what I am trying to do is to get the compliantid, title, status, category for each parent record (compliant) along with all the title and the status of all its child records (issues).
something like:
1011 X openhigh aa open
bb closed
cc open
dd open
1022 A closed high mm closed
1012 Y openmedium null null
Please read the first article I have linked below in my signature block regarding asking for help. Follow the guidelines in the article to post the DDL and sample data. You will get much better answers to your requests for help. I'd like to help, but I don't have time to create your table or modify your sample data for importing.
One thing I would ask, why are you mixing parent and child records in the same table? These should be separated into their own respective tables and that would make joining them together easier.
February 26, 2009 at 4:59 pm
MUCH more helpful. Thank you. Looking at your tables now.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 26, 2009 at 5:21 pm
I see what you were doing with both a parentID and a childId column now, and I would guess that type 2 means child and type 1 means parent. That is really not needed to establish a parent/child relationship. All you need is for each child's parentID to point to the parent itself. If a row has a child pointing to it, then that row is a parent. If a parentID is null, then that row has no parent itself. If a row points to a parent then it is a child.
The following code will work with your schema, and data, as presented. Let me know if you want to discuss simplifying the schema, and what columns should be indexed.
Couple of other items:
The code below will not show orphaned children or childless parents. Should it?
Will a complaint row ever be both a parent AND a child? If so, how do you want that handled?
-----------------------------------------------------------------------------------------
select p.parentCompliantID, p.Title as ParentTitle, p.Status as parentStatus, p.Category as parentCategory, c.title as childTitle, c.status as childStatus
from @compliantTable p
join @compliantTable c on p.childID = c.parentID
where p.type = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply