title: SQL Server INNER JOIN with hierarchyid
body: <p>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.<br>
  The goal of the query is to list items that match the category from the selected category down.</p>
  
  <p>Example:  </p>
  
  <pre><code>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  
  </code></pre>
  
  <p>If the <code>CatID</code> selected is 3, all computer products will be displayed, but if the <code>CatID</code> 6 is selected, then only the printers will be displayed (items with <code>CatID</code> 7, 8, 9 and 10)</p>
  
  <p>I'm rather new to hierarchy id and I have little idea on how to aproach this query.</p>
  
  <p>Thanks to all who contribute.</p>
  
  <p>Carlos</p>
  
  <p>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.</p>
  
  <pre><code>/****** 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
  </code></pre>