SQL Server INNER JOIN with hierarchyid

Question

I have two tables, one table is the items container, which holds all data related to the products. The other table is for categories, in which the data is organised using hierarchies.
The goal of the query is to list items that match the category from the selected category down.

Example:

CatID: 3, Parent: Root, Name: Computers  
CatID: 4, Parent: 3, Name: Laptops  
CatID: 5, Parent: 3, Name: Monitors  
CatID: 6, Parent: 3, Name: Printers  
CatID: 7, Parent: 6, Name: Laser  
CatID: 8, Parent: 6, Name: Ink  
CatID: 9, Parent: 6, Name: Multifunction  
CatID: 10, Parent: 6, Name: Copier

If the CatID selected is 3, all computer products will be displayed, but if the CatID 6 is selected, then only the printers will be displayed (items with CatID 7, 8, 9 and 10)

I'm rather new to hierarchy id and I have little idea on how to aproach this query.

Thanks to all who contribute.

Carlos

Schema of both tables The idea is to join mz_category to ic_item_cat, so a query could list all items for a given category, but also for all child categories. This is intended for a special "home made" treeview where all categories are displayed according to the hierarchy; when a user clicks a category, it displays all items that belong to that category and all items that belong to the child categories as well. Hope you get the idea.

/****** Object:  Table [dbo].[mz_category]    Script Date: 04/26/2012 19:14:34 ******/
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET ARITHABORT ON
GO
/****** Categories Table ******/
CREATE TABLE [dbo].[mz_category](
    [CatNode] [hierarchyid] NOT NULL,
    [CatLevel]  AS ([CatNode].[GetLevel]()),
    [CatID] [int] NOT NULL,
    [CatName] [varchar](80) NOT NULL,
    [SectorId] [varchar](2) NULL,
    [CatIcon] [varchar](255) NULL,
    [oldCat] [varchar](8) NULL,
PRIMARY KEY CLUSTERED 
(
    [CatNode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [CatID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ic_item_cat]    Script Date: 04/26/2012 19:14:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****** Items Table ******/
CREATE TABLE [dbo].[ic_item_cat](
    [item_code] [varchar](25) NOT NULL,
    [item_description] [varchar](80) NULL,
    [item_sector] [varchar](2) NULL,
    [item_line] [varchar](2) NULL,
    [item_reference] [varchar](50) NULL,
    [item_upcean] [varchar](50) NULL,
    [item_category] [int] NULL,
    [item_brand] [varchar](8) NULL,
    [item_cost] [decimal](12, 4) NULL,
    [item_price1] [decimal](12, 4) NULL,
    [item_price2] [decimal](12, 4) NULL,
    [item_price3] [decimal](12, 4) NULL,
    [item_webprice] [decimal](12, 4) NULL,
    [item_dprice1] [decimal](12, 4) NULL,
    [item_dprice2] [decimal](12, 4) NULL,
    [item_dprice3] [decimal](12, 4) NULL,
    [item_lastcost] [decimal](12, 4) NULL,
    [item_lastcostdate] [datetime] NULL,
    [item_lastqtyout] [decimal](12, 4) NULL,
    [item_lastqtyoutdate] [datetime] NULL,
    [item_lastqtyin] [decimal](12, 4) NULL,
    [item_lastqtyindate] [datetime] NULL,
    [item_additionaldesc] [varchar](max) NULL,
    [item_weight] [decimal](12, 4) NULL,
    [item_weight_measure] [varchar](2) NULL,
    [item_width] [decimal](12, 4) NULL,
    [item_width_measure] [varchar](2) NULL,
    [item_length] [decimal](12, 4) NULL,
    [item_length_measure] [varchar](2) NULL,
    [item_height] [decimal](12, 4) NULL,
    [item_height_measure] [varchar](2) NULL,
    [item_whpackdesc] [varchar](80) NULL,
    [item_salespackdesc] [varchar](80) NULL,
    [item_purchpackdesc] [varchar](80) NULL,
    [item_salespackconv] [decimal](12, 4) NULL,
    [item_purchpackconv] [decimal](12, 4) NULL,
    [item_warranty] [varchar](2) NULL,
    [item_delivtime] [varchar](2) NULL,
    [item_rating] [int] NULL,
    [item_vat] [varchar](2) NULL,
    [item_status] [int] NULL,
    [item_avgleadtime] [int] NULL,
    [web_flag] [bit] NULL,
    [partner_id] [varchar](25) NULL,
    [unique_id] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

score 1 · Answer 1 · answered Apr 26 '12 at 08:58

1

You can use a CTE.

Select the starting record(s)
Get the children of these starting record(s) in the recursive part

SQL Statement

;WITH q AS (
  SELECT  CatID, Parent, Name
  FROM    YourTable
  WHERE   CatID = 3
  UNION ALL
  SELECT  t.CatID, t.Parent, t.Name
  FROM    q
          INNER JOIN YourTable t ON t.Parent = q.CatID
)
SELECT  *
FROM    q

answered Apr 26 '12 at 08:58

Lieven Keersmaekers

57,207
13
112
146

Thanks for your input. Perhaps I haven't been clear enough in my post. What I'm trying to achieve is to have a query that the ic_item table (items) lists all items that belong to a category and all childs of that category. I know how to build a query using IsDescendantOf in order to get all childs from the node, but I have no idea how to join it to the items table. – Carlos Apr 26 '12 at 11:29
Sorry, I have no clue what it is you need. Before we engage in a 20 questions game, you should post at a minimum the schema's. Ideal would be to include some testdata and expected outputs. – Lieven Keersmaekers Apr 26 '12 at 13:12
Posted the schema and a brief explanation of what I'm trying to achieve – Carlos Apr 26 '12 at 17:34

Steve Kass · Accepted Answer · 2012-04-26T21:38:14.650

1

Will something like this work?

DECLARE @CatNode hierarchyid;
SET @CatNode = (
    SELECT CatNode from mz_category
    WHERE CatID = @CatID
  );
SELECT * FROM Items
WHERE CatID IN (
  SELECT  CatID
  FROM    mz_category
  WHERE   CatNode.IsDescendantOf(@CatNode) = 1
);

edited Apr 26 '12 at 21:38

answered Apr 26 '12 at 18:28

Steve Kass

7,144
20
26

Unfortunately, It's not good. It displays all row in the items table. Although the code had to be changed, unless you have further explanation for it. @mz_category is not declared, I suposed it was the table name mz-category so I removed the @. – Carlos Apr 26 '12 at 19:18
Oops. Should be IsDescendantOf(@CatNode). Edited. – Steve Kass Apr 26 '12 at 21:38
That worked perfect! Thanks a lot, mate. Now it's time for me to keep on learning and fly solo. – Carlos Apr 27 '12 at 07:24
Also would like to thank all those who sent their suggestions. – Carlos Apr 27 '12 at 07:24
+1 Nice, I thought that hierarchyid was only available in the upcoming Denali version... – Lieven Keersmaekers Apr 27 '12 at 08:29

score 0 · Answer 3 · answered Apr 26 '12 at 09:00

0

DECLARE @CatID hierarchyid = '/6/';

WITH RootCategory AS (
    SELECT  CatID,
            Parent,
            Name
    FROM    Category
    WHERE   CatID = @CatID

    UNION ALL

    SELECT      C.CatID,
                C.Parent,
                C.Name
    FROM        Category C
    INNER JOIN  RootCategory R ON C.Parent = R.CatID
)
SELECT  *
FROM    RootCategory

answered Apr 26 '12 at 09:00

weenoid

1,156
2
11
24

Thanks for your input. Perhaps I haven't been clear enough in my post. What I'm trying to achieve is to have a query that the ic_item table (items) lists all items that belong to a category and all childs of that category. I know how to build a query using IsDescendantOf in order to get all childs from the node, but I have no idea how to join it to the items table. – Carlos Apr 26 '12 at 11:29
1

Could you post the schema for your Items table? It's not possible for me to write that query without this information. – weenoid Apr 26 '12 at 12:05
Posted the schema and a brief explanation of what I'm trying to achieve – Carlos Apr 26 '12 at 17:34

SQL Server INNER JOIN with hierarchyid

3 Answers3