parent and child rows in a self refrencing table i

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

parent and child rows in a self refrencing table i

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-08 : 02:31:58

Select BC.CatID
, BC.Category
, BC.Slug
, BC.ParentID
, Case
When CC.Cnt Is Null Then 0
Else CC.Cnt
End As Cnt
From dbo.Blog_Categories BC
Left Outer Join (Select ec.CatID
, Count(0) As Cnt
From dbo.Blog_Entry_Categories ec
Inner Join dbo.Blog_Entries e
On e.EntryId = ec.EntryId
Group By ec.CatID
, e.Published
Having e.Published = 1) CC
On BC.CatID = CC.CatID

I have a table Blog_Categories which have self refrencing catid and parentid

What i wanted is that the result set should come

item is coming in random order according to catid and parentid

what i want's is that first a catid and it's child come then the second catid and it's child and so on
How can i modify above query

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-08 : 02:51:20

May be this?
ORDER BY ParentID, CatID

One more thing is.. you can use COALESCE instead of CASE statement
COALESCE( CC.Cnt, 0) cnt
instead of Case When Is Null Then 0 Else CC.Cnt End As Cnt

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-08 : 04:33:03

quote:
Originally posted by bandi

May be this?
ORDER BY ParentID, CatID

One more thing is.. you can use COALESCE instead of CASE statement
COALESCE( CC.Cnt, 0) cnt
instead of Case When Is Null Then 0 Else CC.Cnt End As Cnt

Coalesce is fine but actual problem didn't work by order by

--
Chandu

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-08 : 04:48:01

Can you post sample data for 3 tables and also expected output ?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 05:33:16

can blogs categories go multiple lkevels down? like categories,level 1 subcategories,level2 subcategories etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-08 : 07:11:05

At most 1 level down

My sample data is (i have remove slug column)

CatID Category ParentID Cnt

-1 All 0 224
1 Test Defect - Zimmer NexGen CR-Flex Knee 11 1
3 Other 0 12
5 ABC Defense 0 26
6 Family Law 0 4
7 Test Defect - DePuy ASR 11 76
11 Test Defects 0 85
14 Accident and Injury law 0 5
19 Drug Litigation 0 36
20 Drug Litigation - Topamax 19 1
21 Drug Litigation - Depakote 19 1
22 Test Defect - DePuy Pinnacle 11 15
23 Drug Litigation - Actos 19 31
24 Test Defect - Transvaginal Mesh 11 28
25 Texas Warrant Roundups 26 0
26 Traffic Tickets 0 41
27 Drug Litigation - Pradaxa 19 21
28 Test Defect - Biomet M2A 11 1
29 Test Defect - Smith & Nephew R3 11 0
30 Test Defect - Wright Conserve 11 0
31 Test Defect - Stryker Rejuvenate/ABG 11 22
32 Wrongful Death 14 1
33 Immigration 0 1

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-08 : 07:59:18

check this one...

DECLARE @Categories TABLE(CatID INT, Category VARCHAR(50), ParentID INT, Cnt INT)
INSERT INTO @Categories
SELECT -1, 'All', 0, 224 UNION ALL
SELECT 1, 'Test Defect - Zimmer NexGen CR-Flex Knee', 11, 1 UNION ALL
SELECT 3, 'Other', 0, 12 UNION ALL
SELECT 5, 'ABC Defense', 0, 26 UNION ALL
SELECT 6, 'Family Law', 0, 4 UNION ALL
SELECT 7, 'Test Defect - DePuy ASR', 11, 76 UNION ALL
SELECT 11, 'Test Defects', 0, 85 UNION ALL
SELECT 14, 'Accident and Injury law', 0, 5 UNION ALL
SELECT 19, 'Drug Litigation', 0, 36 UNION ALL
SELECT 20, 'Drug Litigation - Topamax', 19, 1 UNION ALL
SELECT 21, 'Drug Litigation - Depakote', 19, 1 UNION ALL
SELECT 22, 'Test Defect - DePuy Pinnacle', 11, 15UNION ALL
SELECT 23, 'Drug Litigation - Actos', 19, 31UNION ALL
SELECT 24, 'Test Defect - Transvaginal Mesh', 11, 28UNION ALL
SELECT 25, 'Texas Warrant Roundups', 26, 0UNION ALL
SELECT 26, 'Traffic Tickets', 0, 41UNION ALL
SELECT 27, 'Drug Litigation - Pradaxa', 19, 21UNION ALL
SELECT 28, 'Test Defect - Biomet M2A', 11, 1UNION ALL
SELECT 29, 'Test Defect - Smith & Nephew R3', 11, 0UNION ALL
SELECT 30, 'Test Defect - Wright Conserve', 11, 0 UNION ALL
SELECT 31, 'Test Defect - Stryker Rejuvenate/ABG', 11, 22UNION ALL
SELECT 32, 'Wrongful Death', 14, 1UNION ALL
SELECT 33, 'Immigration', 0, 1
SELECT ParentID, CatID, Category, cnt
FROM @Categories
ORDER BY ParentID, CatID

--
Chandu

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-08 : 09:04:51

It's wrong result.After Test defect [ATID = 11] row I wnat's it's child rows that is 1,7,22,24 ...

CatID ParentID Category cnt
-1 0 All 224
3 0 Other 12
5 0 ABC Defense 26
6 0 Family Law 4
11 0 Test Defects 85
14 0 Accident and Injury law 5
19 0 Drug Litigation 36
26 0 Traffic Tickets 41
33 0 Immigration 1
1 11 Test Defect - Zimmer NexGen CR-Flex Knee 1
7 11 Test Defect - DePuy ASR 76
22 11 Test Defect - DePuy Pinnacle 15
24 11 Test Defect - Transvaginal Mesh 28
28 11 Test Defect - Biomet M2A 1
29 11 Test Defect - Smith & Nephew R3 0
30 11 Test Defect - Wright Conserve 0
31 11 Test Defect - Stryker Rejuvenate/ABG 22
32 14 Wrongful Death 1
20 19 Drug Litigation - Topamax 1
21 19 Drug Litigation - Depakote 1
23 19 Drug Litigation - Actos 31
27 19 Drug Litigation - Pradaxa 21
25 26 Texas Warrant Roundups 0

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 09:07:50

quote:
Originally posted by bandi

check this one...

DECLARE @Categories TABLE(CatID INT, Category VARCHAR(50), ParentID INT, Cnt INT)
INSERT INTO @Categories
SELECT -1, 'All', 0, 224 UNION ALL
SELECT 1, 'Test Defect - Zimmer NexGen CR-Flex Knee', 11, 1 UNION ALL
SELECT 3, 'Other', 0, 12 UNION ALL
SELECT 5, 'ABC Defense', 0, 26 UNION ALL
SELECT 6, 'Family Law', 0, 4 UNION ALL
SELECT 7, 'Test Defect - DePuy ASR', 11, 76 UNION ALL
SELECT 11, 'Test Defects', 0, 85 UNION ALL
SELECT 14, 'Accident and Injury law', 0, 5 UNION ALL
SELECT 19, 'Drug Litigation', 0, 36 UNION ALL
SELECT 20, 'Drug Litigation - Topamax', 19, 1 UNION ALL
SELECT 21, 'Drug Litigation - Depakote', 19, 1 UNION ALL
SELECT 22, 'Test Defect - DePuy Pinnacle', 11, 15UNION ALL
SELECT 23, 'Drug Litigation - Actos', 19, 31UNION ALL
SELECT 24, 'Test Defect - Transvaginal Mesh', 11, 28UNION ALL
SELECT 25, 'Texas Warrant Roundups', 26, 0UNION ALL
SELECT 26, 'Traffic Tickets', 0, 41UNION ALL
SELECT 27, 'Drug Litigation - Pradaxa', 19, 21UNION ALL
SELECT 28, 'Test Defect - Biomet M2A', 11, 1UNION ALL
SELECT 29, 'Test Defect - Smith & Nephew R3', 11, 0UNION ALL
SELECT 30, 'Test Defect - Wright Conserve', 11, 0 UNION ALL
SELECT 31, 'Test Defect - Stryker Rejuvenate/ABG', 11, 22UNION ALL
SELECT 32, 'Wrongful Death', 14, 1UNION ALL
SELECT 33, 'Immigration', 0, 1
SELECT ParentID, CatID, Category, cnt
FROM @Categories
ORDER BY ParentID, CatID

--
Chandu

see the difference


DECLARE @Categories TABLE(CatID INT, Category VARCHAR(50), ParentID INT, Cnt INT)
INSERT INTO @Categories
SELECT -1, 'All', 0, 224 UNION ALL
SELECT 1, 'Test Defect - Zimmer NexGen CR-Flex Knee', 11, 1 UNION ALL
SELECT 3, 'Other', 0, 12 UNION ALL
SELECT 5, 'ABC Defense', 0, 26 UNION ALL
SELECT 6, 'Family Law', 0, 4 UNION ALL
SELECT 7, 'Test Defect - DePuy ASR', 11, 76 UNION ALL
SELECT 11, 'Test Defects', 0, 85 UNION ALL
SELECT 14, 'Accident and Injury law', 0, 5 UNION ALL
SELECT 19, 'Drug Litigation', 0, 36 UNION ALL
SELECT 20, 'Drug Litigation - Topamax', 19, 1 UNION ALL
SELECT 21, 'Drug Litigation - Depakote', 19, 1 UNION ALL
SELECT 22, 'Test Defect - DePuy Pinnacle', 11, 15UNION ALL
SELECT 23, 'Drug Litigation - Actos', 19, 31UNION ALL
SELECT 24, 'Test Defect - Transvaginal Mesh', 11, 28UNION ALL
SELECT 25, 'Texas Warrant Roundups', 26, 0UNION ALL
SELECT 26, 'Traffic Tickets', 0, 41UNION ALL
SELECT 27, 'Drug Litigation - Pradaxa', 19, 21UNION ALL
SELECT 28, 'Test Defect - Biomet M2A', 11, 1UNION ALL
SELECT 29, 'Test Defect - Smith & Nephew R3', 11, 0UNION ALL
SELECT 30, 'Test Defect - Wright Conserve', 11, 0 UNION ALL
SELECT 31, 'Test Defect - Stryker Rejuvenate/ABG', 11, 22UNION ALL
SELECT 32, 'Wrongful Death', 14, 1UNION ALL
SELECT 33, 'Immigration', 0, 1
SELECT ParentID, CatID, Category, cnt
FROM @Categories
ORDER BY CASE WHEN ParentID=0 THEN CatID ELSE ParentID END,ParentID


output
---------------------------------------------
ParentID	CatID	Category	cnt
---------------------------------------------
0	-1	All	224
0	3	Other	12
0	5	ABC Defense	26
0	6	Family Law	4
0	11	Test Defects	85
11	7	Test Defect - DePuy ASR	76
11	1	Test Defect - Zimmer NexGen CR-Flex Knee	1
11	22	Test Defect - DePuy Pinnacle	15
11	24	Test Defect - Transvaginal Mesh	28
11	28	Test Defect - Biomet M2A	1
11	29	Test Defect - Smith & Nephew R3	0
11	30	Test Defect - Wright Conserve	0
11	31	Test Defect - Stryker Rejuvenate/ABG	22
0	14	Accident and Injury law	5
14	32	Wrongful Death	1
0	19	Drug Litigation	36
19	20	Drug Litigation - Topamax	1
19	21	Drug Litigation - Depakote	1
19	27	Drug Litigation - Pradaxa	21
19	23	Drug Litigation - Actos	31
0	26	Traffic Tickets	41
26	25	Texas Warrant Roundups	0
0	33	Immigration	1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-08 : 09:20:39

Thanks a lot one more thing

Can i somehow get result of Alphabetical order pranet category first then it's child category

Like

---------------------------------------------
ParentID CatID Category cnt
---------------------------------------------
0 5 ABC Defense 26
0 14 Accident and Injury law 5
14 32 Wrongful Death 1
0 -1 All 224
0 19 Drug Litigation 36
19 20 Drug Litigation - Topamax 1
19 21 Drug Litigation - Depakote 1
19 27 Drug Litigation - Pradaxa 21
19 23 Drug Litigation - Actos 31
0 6 Family Law 4
0 33 Immigration 1
0 3 Other 12
0 11 Test Defects 85
11 7 Test Defect - DePuy ASR 76
11 1 Test Defect - Zimmer NexGen CR-Flex Knee 1
11 22 Test Defect - DePuy Pinnacle 15
11 24 Test Defect - Transvaginal Mesh 28
11 28 Test Defect - Biomet M2A 1
11 29 Test Defect - Smith & Nephew R3 0
11 30 Test Defect - Wright Conserve 0
11 31 Test Defect - Stryker Rejuvenate/ABG 22
0 26 Traffic Tickets 41
26 25 Texas Warrant Roundups 0

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 09:27:35

how is this as per alphabetical order of parent? how did Other come after Traffic tikets? also Family Law after Immigration

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 01:25:25

quote:
Originally posted by kamii47

As o comes after t and I comes after F
o doesn't has any child so did F

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

then hos is this alphabetic? in English alphabets o comes before t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-11 : 01:49:47

It was a typo and wrong sample data.I have corrected the sample data.

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-03-11 : 06:02:02

I have resolved my problem

Select BC.CatID
, Case
When ParentID = 0 Then Bc.Category
Else '---' + BC.CAtegory
End As Category
, BC.Slug
, BC.ParentID
, Case
When CC.Cnt Is Null Then 0
Else CC.Cnt
End As Cnt
From dbo.Blog_Categories BC
Left Outer Join (Select ec.CatID
, Count(0) As Cnt
From dbo.Blog_Entry_Categories ec
Inner Join dbo.Blog_Entries e
On e.EntryId = ec.EntryId
Group By ec.CatID
, e.Published
Having e.Published = 1) CC
On BC.CatID = CC.CatID
Where BC.PortalID = 1
Order By Case
When ParentID = 0 Then CAtegory
Else (Select CAtegory
From dbo.Blog_Categories parent
Where parent.CatID = bc.ParentID)
End
, Case
When ParentID = 0 Then 1
End Desc
, Category

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 06:34:32

ok...great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources