CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
rosko
rosko

Reputation: 474

SQL Server : query for obtaining products from nested categories hierarchy

I've got a problem with my SQL query. What I have here is two SQL Server tables.

The first one:

CREATE TABLE [category].[Categories](
    [Id] [bigint] IDENTITY(1,1) NOT NULL, -- PRIMARY KEY
    [Name] [nvarchar](200) NOT NULL,
    [ParentId] [bigint] NULL -- CONSTRAINT [FK_Categories_Categories] FOREIGN KEY([ParentId])
);

and the second one is:

CREATE TABLE [product].[Products](
    [Id] [bigint] IDENTITY(1,1) NOT NULL, -- PRIMARY KEY
    [Name] [nvarchar](250) NOT NULL,
    [CategoryId] [bigint] NOT NULL, -- CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryId])
    [Description] [nvarchar](500) NULL,
    [ManufacturerUrl] [nvarchar](1000) NULL,
    [ImageUrl] [nvarchar](1000) NULL,
    [Price] [money] NOT NULL
);

Lets say there is a category hierarchy:

What I want is to be able to select products by specifying just the category id.

e.g.: Lets say we want to get all the products under category with id = 1
Results: We get all the products from categories like: Cameras, Computers, Cell phones with (cell phones and smartphones + Cell phone accessories)

e.g.2: Lets say we want to get all the products under category with id = 5
Results: We get all the products from: Cell phones and smartphones + Cell phone accessories)

As for now I only have this query:

With Categories_CTE As
(
    Select Id, Name, ParentId
    From category.Categories
    Where Id = **SOME CATEGORY ID**

    Union All
    Select t.Id, t.Name, t.ParentId
    From category.Categories t
    Inner Join Categories_CTE c On c.Id = t.ParentId
)

Select Id, Name, ParentId
From Categories_CTE;

Which gets me the hierarchy of categories but this isn't what I really want...

So, is there anybody who can help me?

Thank you in advance.

Upvotes: 2

Views: 2040

Answers (2)

the_pete
the_pete

Reputation: 822

This should work, but without data to test I can't be certain:

select p.*
from products as p
left join categories as c on (c.id = p.categoryid or c.parentid = p.categoryid)
where p.categoryid = 1

Like @Matt Whitfield said, you are going to want to do a join, but using left join here and the or clause allows you to find items that are also associated to just the parent IDs in case something weird happens where an item is just added under "Electronics." At least that's the hope as without date it's a little hard to do a live test.

Upvotes: 0

Matt Whitfield
Matt Whitfield

Reputation: 6584

Just join onto products from where you were...

With Categories_CTE As
(
    Select Id, Name, ParentId
    From category.Categories
    Where Id = **SOME CATEGORY ID**

    Union All
    Select t.Id, t.Name, t.ParentId
    From category.Categories t
    Inner Join Categories_CTE c On c.Id = t.ParentId
)

Select p.*
From Categories_CTE c INNER JOIN product.Products p on p.CategoryId = c.Id;

NOTE: don't actually use p.* in your production code, specify the columns explicitly.

Upvotes: 5

Related Questions