CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
gyromonotron
gyromonotron

Reputation: 1121

How to get all parent and child categories that each product belongs to?

I have 3 tables:

CREATE TABLE [dbo].[ProductCategory](
 [categoryID] [int] IDENTITY(1,1) NOT NULL,
 [categoryParentID] [int] NULL,
 [categoryName] [nvarchar](200) NOT NULL
)

CREATE TABLE [dbo].[Product]
(
 [productID] [bigint] IDENTITY(1,1) NOT NULL,
 [productName] [nvarchar(100)] NOT NULL,
 .
 .
)

CREATE TABLE [dbo].[ProductToCategoryLink]
(
 [productID] [bigint] NOT NULL,
 [categoryID] [int] NOT NULL
)

Sample data example:

Product:

1, 'Book_1';

2, 'Book_2';

ProductCategory:

1, NULL, 'Books';

2, 1, 'Books Subcategory lvl_1';

3, 2, 'Books Subcategory lvl_2';

4, 3, 'Books Subcategory lvl_3';

ProductToCategoryLink:

1, 4;

2, 2;

Question: How to get all parent and child categories that each product belongs to?

So, i need to get something like this:

productID, productName, categoryID, categoryName

1, 'Book_1', 1, 'Books';

1, 'Book_1', 2, 'Books Subcategory lvl_1';

1, 'Book_1', 3, 'Books Subcategory lvl_2';

1, 'Book_1', 4, 'Books Subcategory lvl_3';

2, 'Book_2', 1, 'Books';

2, 'Book_2', 2, 'Books Subcategory lvl_1';

Upvotes: 3

Views: 4310

Answers (2)

WReach
WReach

Reputation: 18271

The SQL query exhibited at the bottom of this post should do the trick, if verbosely.

The key feature is the recursive definition of the hierarchy table expression. The first SELECT retrieves all product-category links, along with their names and category parent IDs. This is the base case of the recursion. The second SELECT finds all of the category parent rows for all of the rows found in the previous step. Note particularly that the second SELECT joins to the hierarchy expression again, making the query recursive. SQL Server will repeatedly evaluate the second SELECT until no new records are found. The third SELECT statement simply returns the results.

As you might expect, it would be a bad thing if there were cycles in the category parent chain. SQL Server limits the number of times that it will recurse before it abandons the query. The default limit is 100, which is probably enough unless the category hierarchy is outrageously deep. If necessary, you can raise it up to as much as 32767 using the MAXRECURSION query option.

Here is the SQL query:

WITH
  hierarchy AS (
    SELECT
      prod.productId
    , prod.productName
    , cat.categoryId
    , cat.categoryParentId
    , cat.categoryName
    FROM dbo.ProductToCategoryLink AS link
    INNER JOIN dbo.Product AS prod
      ON prod.productId = link.productId
    INNER JOIN dbo.ProductCategory AS cat
      ON cat.categoryId = link.categoryId
    UNION ALL
    SELECT
      child.productId
    , child.productName
    , parent.categoryId
    , parent.categoryParentId
    , parent.categoryName
    FROM hierarchy AS child
    INNER JOIN dbo.ProductCategory AS parent
      ON parent.categoryId = child.categoryParentId
  )
SELECT
  productId
, productName
, categoryId
, categoryName
FROM hierarchy
ORDER BY
  productId
, categoryId

Upvotes: 1

TToni
TToni

Reputation: 9391

The easiest way is through common table expressions. >Link<

Use the direct link as the anchor, then expand twice: First expand the parents of the found anchors, then with a second union all expand the children.

Upvotes: 3

Related Questions