| Author |
 Topic  |
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
 Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/08/2013 : 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
335 Posts |
Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/08/2013 : 04:48:01
|
Can you post sample data for 3 tables and also expected output ?
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/08/2013 : 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
335 Posts |
Posted - 03/08/2013 : 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)
|
Edited by - kamii47 on 03/08/2013 07:24:42 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/08/2013 : 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
335 Posts |
Posted - 03/08/2013 : 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)
|
Edited by - kamii47 on 03/08/2013 09:05:32 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/08/2013 : 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
335 Posts |
Posted - 03/08/2013 : 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)
|
Edited by - kamii47 on 03/11/2013 01:48:51 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 03/08/2013 : 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
India
47157 Posts |
Posted - 03/11/2013 : 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/
|
Edited by - visakh16 on 03/11/2013 01:25:55 |
|
|
|
kamii47
Constraint Violating Yak Guru
335 Posts |
Posted - 03/11/2013 : 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
335 Posts |
Posted - 03/11/2013 : 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
India
47157 Posts |
Posted - 03/11/2013 : 06:34:32
|
ok...great
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
parent and child rows in a self refrencing table i
