I have inherited a very badly-maintained MS SQL database and I need some help finding data that hasn't been correctly entered.
Here's the structure (relevant bits only):
products
product_id (PK)
manufacturer_id
(each product has ONE manufacturer)
manufacturers
manufacturer_id (PK)
name
categories
category_id (PK)
parent_id
product_to_categories (Many-to-Many: no PK)
product_id
category_id
(each product CAN exist in more than one category; each category DOES hold more than one product)
Right, the important thing to note (that isn't obvious here) is that categories are nested. Each level1 category contains about 10 level 2 categories and each level 2 category contains about 8 level 3 categories. The hierachical path between them is controlled
by the "parent_id" column like so:
Level 1 Category: might have category_id=45
ALL of this categories sub(level 2) categories will have their own category_id's but the SAME parent_id = 45.
Likewise the parent_id of ALL the Level 3 categories beneath a specific Level 2 category will have that Level 2's category_id as THEIR parent_id.
With me? Like a family tree.
*******
My problem is that, when products have been entered into the database, they should have been (in the "products_to_categories" table) associated with a Level THREE category. Some, however, have had their product_id associated with Level TWO categories by
mistake. This makes them not show up at all in the web site as the perfectly valid SQL I'm using to display them is looking inside Level THREE categories.
I need a SQL statement which I can run in SQL Server Management Studio which will find these wronly placed products so that I can move them.
The basic identifier would be that these products have a Level 2 category_id in products_to_categories and THAT id can be identified as being the immediate child of a Level 1 id.
*******
OK. That makes sense in English. Do you think I can translate that to a SQL SELECT statement? Not a hope.
WITH cte AS (
SELECT
category_id, parent_id, 0 AS level
FROM categories
UNION ALL
SELECT
c.category_id, c.parent_id, (CTE_2.level + 1) AS level
FROM categories AS c
INNER JOIN CTE AS CTE_2 ON CTE_2.category_id = c.parent_id
)
SELECT category_id, parent_id, level
FROM cte AS cte_1
After you join product-categories table with this select, you will have the ability to see the level of the product categories for each product
I have looked at the link. This is a little over my head but I am working through it and looking at similar sites to understand this technique.
According to the MSDN site, this code is causing an infinite loop. Even setting OPTION (MAXRECURSION 2000); is still exhausted.
Changing your code to what MSDN recommend to avoid infinite looping produces an error:
WITH cte (category_id, parent_id, 0 AS level)
AS
(
SELECT category_id, parent_id, 0 AS level
FROM categories
UNION ALL
SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level
FROM categories AS c
INNER JOIN CTE AS CTE_2 ON CTE_2.category_id = c.parent_id
)
SELECT category_id, parent_id, level
FROM cte AS cte_1;
I am getting the error: "Incorrect syntax near '0'." on line 1.
WITH cte (category_id, parent_id, level)
AS
(
SELECT category_id, parent_id, 0 AS level
FROM categories
UNION ALL
SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level
FROM categories AS c
INNER JOIN CTE AS CTE_2 ON CTE_2.category_id = c.parent_id
)
SELECT category_id, parent_id, level
FROM cte AS cte_1;
produces: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
WITH cte (category_id, parent_id, level)
AS
(
SELECT category_id, parent_id, 0 AS level
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level
FROM categories AS c
INNER JOIN cte AS CTE_2 ON CTE_2.category_id = c.parent_id
WHERE c.parent_id IS NOT NULL
)
SELECT category_id, parent_id, level
FROM cte;
WITH cte (category_id, parent_id, level)
AS
(
SELECT category_id, parent_id, 1 AS level
FROM categories
WHERE parent_id = 0 AND active = 'y'
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 products_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 < 4 AND p.active = 'y'
ORDER BY level
Not sure why I need "1 AS Level" instead of "0 AS Level" though.
In my opinion, there is no need to make the "1 AS Level" instead of "0 AS Level". The reason why your code doesn't work is the parent_id
is not correct. Have you tested "0 AS Level" and seen whether it worked?
Best wishes,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
banksidepoet
Participant
774 Points
862 Posts
SELECT statement needed.
Nov 23, 2012 12:19 PM|LINK
Hi,
I have inherited a very badly-maintained MS SQL database and I need some help finding data that hasn't been correctly entered.
Here's the structure (relevant bits only):
products
product_id (PK)
manufacturer_id
(each product has ONE manufacturer)
manufacturers
manufacturer_id (PK)
name
categories
category_id (PK)
parent_id
product_to_categories (Many-to-Many: no PK)
product_id
category_id
(each product CAN exist in more than one category; each category DOES hold more than one product)
Right, the important thing to note (that isn't obvious here) is that categories are nested. Each level1 category contains about 10 level 2 categories and each level 2 category contains about 8 level 3 categories. The hierachical path between them is controlled by the "parent_id" column like so:
Level 1 Category: might have category_id=45
ALL of this categories sub(level 2) categories will have their own category_id's but the SAME parent_id = 45.
Likewise the parent_id of ALL the Level 3 categories beneath a specific Level 2 category will have that Level 2's category_id as THEIR parent_id.
With me? Like a family tree.
*******
My problem is that, when products have been entered into the database, they should have been (in the "products_to_categories" table) associated with a Level THREE category. Some, however, have had their product_id associated with Level TWO categories by mistake. This makes them not show up at all in the web site as the perfectly valid SQL I'm using to display them is looking inside Level THREE categories.
I need a SQL statement which I can run in SQL Server Management Studio which will find these wronly placed products so that I can move them.
The basic identifier would be that these products have a Level 2 category_id in products_to_categories and THAT id can be identified as being the immediate child of a Level 1 id.
*******
OK. That makes sense in English. Do you think I can translate that to a SQL SELECT statement? Not a hope.
Help, please.
eralper
Contributor
6048 Points
971 Posts
Re: SELECT statement needed.
Nov 23, 2012 01:07 PM|LINK
Hello,
You can get category level using following query
After you join product-categories table with this select, you will have the ability to see the level of the product categories for each product
You can further check http://www.kodyaz.com/t-sql/sql-server-recursive-query-with-recursive-cte.aspx for recursive query samples and query structure for SQL Server
SQL Server 2012
banksidepoet
Participant
774 Points
862 Posts
Re: SELECT statement needed.
Nov 23, 2012 01:15 PM|LINK
Thank you for your response.
I have looked at the link. This is a little over my head but I am working through it and looking at similar sites to understand this technique.
According to the MSDN site, this code is causing an infinite loop. Even setting OPTION (MAXRECURSION 2000); is still exhausted.
Changing your code to what MSDN recommend to avoid infinite looping produces an error:
I am getting the error: "Incorrect syntax near '0'." on line 1.
Any ideas?
TabAlleman
All-Star
15575 Points
2702 Posts
Re: SELECT statement needed.
Nov 23, 2012 05:08 PM|LINK
change the first line to this:
WITH cte (category_id, parent_id, level)
banksidepoet
Participant
774 Points
862 Posts
Re: SELECT statement needed.
Nov 23, 2012 05:23 PM|LINK
TabAlleman, Thanks for your response.
This code:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 0 AS level FROM categories UNION ALL SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level FROM categories AS c INNER JOIN CTE AS CTE_2 ON CTE_2.category_id = c.parent_id ) SELECT category_id, parent_id, level FROM cte AS cte_1;produces: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
TabAlleman
All-Star
15575 Points
2702 Posts
Re: SELECT statement needed.
Nov 23, 2012 05:54 PM|LINK
Try this:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 0 AS level FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.category_id, c.parent_id, (CTE_2.level + 1) AS level FROM categories AS c INNER JOIN cte AS CTE_2 ON CTE_2.category_id = c.parent_id WHERE c.parent_id IS NOT NULL ) SELECT category_id, parent_id, level FROM cte;banksidepoet
Participant
774 Points
862 Posts
Re: SELECT statement needed.
Nov 23, 2012 06:17 PM|LINK
No error but no records returned either.
Catherine Sh...
All-Star
23382 Points
2490 Posts
Microsoft
Re: SELECT statement needed.
Nov 30, 2012 04:24 AM|LINK
Hi,
In order to resolve your issue, please try to check the value of parent_id at first. Then test the code below and see whether it returns some records.
If there is no data which is returned, that means there is no data which parent_id is null.
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store
banksidepoet
Participant
774 Points
862 Posts
Re: SELECT statement needed.
Nov 30, 2012 07:54 AM|LINK
Catherine Shan,
Thank you for your response.
Here is the code I'm using which seems to work:
WITH cte (category_id, parent_id, level) AS ( SELECT category_id, parent_id, 1 AS level FROM categories WHERE parent_id = 0 AND active = 'y' 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 products_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 < 4 AND p.active = 'y' ORDER BY levelNot sure why I need "1 AS Level" instead of "0 AS Level" though.
Catherine Sh...
All-Star
23382 Points
2490 Posts
Microsoft
Re: SELECT statement needed.
Nov 30, 2012 08:11 AM|LINK
Hi,
In my opinion, there is no need to make the "1 AS Level" instead of "0 AS Level". The reason why your code doesn't work is the parent_id is not correct. Have you tested "0 AS Level" and seen whether it worked?
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store