SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 parent and child rows in a self refrencing table i
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/08/2013 :  02:31:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/08/2013 :  02:51:20  Show Profile  Reply with Quote
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
Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/08/2013 :  04:33:03  Show Profile  Reply with Quote
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)

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/08/2013 :  04:48:01  Show Profile  Reply with Quote
Can you post sample data for 3 tables and also expected output ?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/08/2013 :  05:33:16  Show Profile  Reply with Quote
can blogs categories go multiple lkevels down? like categories,level 1 subcategories,level2 subcategories etc?

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

Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/08/2013 :  07:11:05  Show Profile  Reply with Quote
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)


Edited by - kamii47 on 03/08/2013 07:24:42
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/08/2013 :  07:59:18  Show Profile  Reply with Quote
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
Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/08/2013 :  09:04:51  Show Profile  Reply with Quote
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)


Edited by - kamii47 on 03/08/2013 09:05:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/08/2013 :  09:07:50  Show Profile  Reply with Quote
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/

Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/08/2013 :  09:20:39  Show Profile  Reply with Quote
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)


Edited by - kamii47 on 03/11/2013 01:48:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/08/2013 :  09:27:35  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/11/2013 :  01:25:25  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 03/11/2013 01:25:55
Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/11/2013 :  01:49:47  Show Profile  Reply with Quote
It was a typo and wrong sample data.I have corrected the sample data.


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

Go to Top of Page

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 03/11/2013 :  06:02:02  Show Profile  Reply with Quote
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)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/11/2013 :  06:34:32  Show Profile  Reply with Quote
ok...great

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000