... which explains the intial problem I posted. What I need is an extra column (product_id) in the output of your code which shows what level each PRODUCT is at. They should ALL be Level 3 but I know some have been incorrectly entered at Level 2 by the people
I inherited this db from and I need to identify those.
WITH cte (category_id, parent_id, level)
AS
(
SELECT category_id, parent_id, 1 AS level
FROM categories
WHERE parent_id = 0
UNION ALL
SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level
FROM CTE AS CTE_2
INNER JOIN categories AS c ON c.parent_id = CTE_2.category_id
)
SELECT p.product_id, pc.category_id, c.level
FROM products as p
JOIN product_to_categories as pc
on pc.product_id = p.product_id
JOIN cte as c
on c.category_id = pc.category_id
where c.Level < 3
SELECT category_id, parent_id, 1 AS level
FROM categories
WHERE parent_id = 0
Hello again.
One question... imagining my "family tree" of categories, I have about 8 categories with a parent_id = 0 (i.e. Level 1 categories, each having their own unique category_id value.). Suppose I just want to limit the resiult set of the query to ONE Level 1
category and its descendants.
I have a feeling it's the code above that needs changing.. Do I need to change parent_id to the category_id of the Level 1 category? But then I'm starting the recursion one level lower down so how would this affect the remainder of the code?
WITH cte (category_id, parent_id, level)
AS
(
SELECT category_id, parent_id, 1 AS level
FROM categories
WHERE category_id = @CatIdLevel1
UNION ALL
-- ...
ps: thank you for all my posts marked as answer, but I believe it would be more correct mark only the last
That's what I thought. Does any of the rest of the code need changing on the grounds that I am now starting the recursion one level deeper (Level 2 in the tree)?
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 23, 2012 08:25 PM|LINK
OK, I've got data. I now know what level each category is at. Thank you very much for that.
Would you take a look at the first post, here: http://forums.asp.net/t/1860893.aspx/1?SELECT+statement+needed+
... which explains the intial problem I posted. What I need is an extra column (product_id) in the output of your code which shows what level each PRODUCT is at. They should ALL be Level 3 but I know some have been incorrectly entered at Level 2 by the people I inherited this db from and I need to identify those.
Thank you very much for this help.
spapim
Contributor
2464 Points
372 Posts
Re: Common Table Expression Usage
Nov 23, 2012 08:54 PM|LINK
Try:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 1 AS level FROM categories WHERE parent_id = 0 UNION ALL SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level FROM CTE AS CTE_2 INNER JOIN categories AS c ON c.parent_id = CTE_2.category_id ) SELECT p.product_id, pc.category_id, c.level FROM products as p JOIN product_to_categories as pc on pc.product_id = p.product_id JOIN cte as c on c.category_id = pc.category_id where c.Level < 3Hope this helps.
www.imobiliariasemsuzano.com.br
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 23, 2012 09:08 PM|LINK
You are a star!
I want to say a special thank you for sticking with me through a technique I had no knowledge of yesterday.
Thank you VERY much!
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 24, 2012 06:39 AM|LINK
Hello again.
One question... imagining my "family tree" of categories, I have about 8 categories with a parent_id = 0 (i.e. Level 1 categories, each having their own unique category_id value.). Suppose I just want to limit the resiult set of the query to ONE Level 1 category and its descendants.
I have a feeling it's the code above that needs changing.. Do I need to change parent_id to the category_id of the Level 1 category? But then I'm starting the recursion one level lower down so how would this affect the remainder of the code?
Thanks, once more.
spapim
Contributor
2464 Points
372 Posts
Re: Common Table Expression Usage
Nov 24, 2012 12:42 PM|LINK
Try:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 1 AS level FROM categories WHERE category_id = @CatIdLevel1 UNION ALL -- ...ps: thank you for all my posts marked as answer, but I believe it would be more correct mark only the last
Hope this helps.
www.imobiliariasemsuzano.com.br
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 24, 2012 01:04 PM|LINK
Thank you. (I have been advised to mark ALL parts of an answer As Answer). Not sure which it is.
Right, to code:
Getting an error:
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@CatIdLevel1".
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
spapim
Contributor
2464 Points
372 Posts
Re: Common Table Expression Usage
Nov 24, 2012 02:53 PM|LINK
Replace @CatIdLevel1 by an Id of a level 1 category. Ex:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 1 AS level FROM categories WHERE category_id = 19 UNION ALL -- ...Hope this helps.
www.imobiliariasemsuzano.com.br
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 24, 2012 03:07 PM|LINK
That's what I thought. Does any of the rest of the code need changing on the grounds that I am now starting the recursion one level deeper (Level 2 in the tree)?
spapim
Contributor
2464 Points
372 Posts
Re: Common Table Expression Usage
Nov 24, 2012 09:41 PM|LINK
No.
www.imobiliariasemsuzano.com.br
banksidepoet
Participant
774 Points
862 Posts
Re: Common Table Expression Usage
Nov 25, 2012 03:54 AM|LINK
That's it then, I can now achieve what I need.
You've been really helpful. Thank you very much for your perseverance.