November 10, 2010 at 2:05 pm
Hi learned people,
I have the following tables:
Category <---This has the tree structure for the category listing
CategoryIDParentCategoryID
CategoryIDParentCategoryID
Product_Category_Mapping <--- This maps products to a category
ProductCategoryID ProductID CategoryID
How does one count all the products under each parent category including the products under the parents sub categories?!
Thanks
November 10, 2010 at 2:13 pm
Use a recursive CTE to crawl the hierarchy (Books Online has an example of how to do this), and retain the top level ParentID as a third column in it. Then the outer query selects that column and does the usual count and group by actions.
- 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
November 10, 2010 at 2:33 pm
CELKO (11/10/2010)
For example, there is no such thing as a "category_id" -- an attribute can a "<something>_category" or a "<something>_id" , but not that nonsense.
Sure there is, watch:
CREATE TABLE Category
(CategoryID INT IDENTITY( 1,1),
CategoryNameVARCHAR(100),
CategoryDescVARCHAR(2000),
IsActiveBIT
)
It's very easy to have a CategoryID.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 3:22 pm
Wow thansk for the fast replies all.
Unfortunatley I enherited this DB and am stuck with it. My research did indicate nested would be better but no option to redo it.
I do have a CTE so far for the Categories but I cant seem to figure out how to add the Product_Category_Mapping into the mix in order to do the count i need:
;WITH ChildrenCTE AS (
SELECT RootID = CategoryID, CategoryID
FROM dbo.Category
UNION ALL
SELECT cte.RootID, d.CategoryID
FROM ChildrenCTE cte
INNER JOIN dbo.Category d ON d.ParentCategoryID = cte.CategoryID
)
SELECT d.CategoryID, d.ParentCategoryID, d.Name, cnt.Children
FROM dbo.Category d
INNER JOIN (
SELECT CategoryID = RootID, Children = COUNT(*) - 1
FROM ChildrenCTE
GROUP BY RootID
) cnt ON cnt.CategoryID = d.CategoryID
November 11, 2010 at 6:27 am
Try something like this out, see if it does what you need:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T (
ID INT IDENTITY PRIMARY KEY,
ParentID INT NULL);
INSERT INTO #T (ParentID)
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT NULL;
SELECT * FROM #T;
;WITH CTE (ID, ParentID, TopID) AS
(SELECT ID, ID, ID
FROM #T
WHERE ParentID IS NULL
UNION ALL
SELECT T2.ID, T2.ParentID, CTE.TopID
FROM #T AS T2
INNER JOIN CTE
ON T2.ParentID = CTE.ID)
SELECT TopID, COUNT(*) AS ChildNodes
FROM CTE
GROUP BY TopID
ORDER BY TopID;
- 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
November 11, 2010 at 10:47 am
CELKO (11/11/2010)
LOL! You even used a physical count of insertions attempts in that table! No key, no way to have a key, insanely long columns to invite garbage data, and BIT flags! You are close to the record for bad SQL in actual production, but not the champ. I clipped an example from a posting that was so bad that if I had used it in one of my books, my reviewers would have accused me of being over the top and making up stuff.Try to see how many more violations of ISO Standards, Dr. Codd's rules and proper data modeling you can get in the fewest lines of code. One you missed was the use of special characters, especially spaces, in square brackets. That will prevent any hope of a data dictionary across the enterprise.
Oh, sorry, knew I missed something...
CREATE TABLE Category
(CategoryID INT IDENTITY( 1,1),
[Category Name] VARCHAR(100),
[Category Desc] VARCHAR(2000),
IsActive BIT,
CONSTRAINT pk_Category PRIMARY KEY NONCLUSTERED (CategoryID)
)
CREATE CLUSTERED INDEX idx_Category ON Category ( IsActive, [Category Name])
There we go.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 10:58 am
Craig, you need to include a column name that's an SQL keyword, at the very least.
billyholliday: Ignoring Joe's usual trollishness, and Craig's humor, did my sample hierarchy crawl get you what you need?
- 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
November 11, 2010 at 11:33 am
Thanks for your reply GS
I will try it out soon but have not had a chance yet. You have been most helpfull unlike some others here.
November 11, 2010 at 1:25 pm
I tried your version GS but got no results. Obviously I am inexperienced in these matters.
I have decided to attach two tab delimited files for the original data from the db as this may help better see what i am faced with......... 🙂
Thank you again for your help in this regard.
And yes I do battle to follow this kind of SQL. Maybe in addition you could point me to an easy to understand explanation with examples so I can get a better grip on it.
November 11, 2010 at 2:37 pm
;WITH CTE (ID, ParentID, TopID) AS
(SELECT CategoryID, ParentCategoryID, CategoryID
FROM dbo.Category
WHERE ParentCategoryID = 0
UNION ALL
SELECT T2.CategoryID, T2.ParentCategoryID, CTE.TopID
FROM dbo.Category AS T2
INNER JOIN CTE
ON T2.ParentCategoryID = CTE.ID)
SELECT TopID as TopCategoryID, COUNT(*)-1 AS ChildCategories
FROM CTE
GROUP BY TopID;
That will give you the top level category IDs (the one's that have a parent of 0), and how many total child categories each has.
- 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
November 11, 2010 at 11:05 pm
Thank you very much for that simple but effective SQL. I can see why you have so many posts....... 🙂
Only thing is that this only counts the category/subcategory totals and I actually need it to count the products attached to those categoies in the same manner and hence the second tab delimited file or am i missing something...........
Can it be done with the data i sen or is it to bad?
Really appreciate your help.
November 12, 2010 at 9:38 am
In the final (outer) query, you can join to the products table and do the count that way. That will give you the number of products per TopCategoryID. Is that what you need?
- 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
November 14, 2010 at 8:34 pm
CELKO (11/12/2010)
Giving you recursive kludges for an adjacency list is not really helping you. [font="Arial Black"]You are not learning how to think in RDBMS and sets.[/font]8. To convert an adjacency list to a nested set model, [font="Arial Black"]use a push down stack[/font].
[font="Arial Black"]BWAA_HAAA!!!![/font] Listen to the pot calling the kettle black. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply