sql cte query with closure table

The provided SQL script creates two tables, "categories" and "childcategories," and inserts some sample data into them. It then uses a recursive common table expression (CTE) to generate a breadcrumb-like structure that shows the hierarchical relationships between parent and child categories. Let's go through the script step by step:

1. Creating the "categories" table: This table stores different categories and has the following columns:

• id: An auto-incrementing primary key for each category.
• created_date: The date the category is created, set to the current date and time by default.
• updated_date: The date the category is last updated, set to the current date and time by default.
• title: The name of the category (e.g., hardware, cpu, gpu, intel, amd, nvidia, geforce).

2. Creating the "childcategories" table: This table defines the many-to-many relationship between parent and child categories. It has the following columns:

• parent_id: The ID of the parent category.
• child_id: The ID of the child category.

3. Inserting data into the "categories" table: Some sample categories are inserted into the "categories" table.

4. Inserting data into the "childcategories" table: Some sample relationships between parent and child categories are inserted into the "childcategories" table.

5. Using a recursive CTE to generate breadcrumbs: The CTE (cte) is used to recursively traverse the hierarchical relationships between parent and child categories and generate breadcrumb-like strings representing the hierarchy. The CTE has the following columns:

• id: The ID of the category.
• parent_title: The title (name) of the parent category.
• level: The level of the category in the hierarchy (1 for top-level, 2 for child of top-level, and so on).
• child_title: The title (name) of the child category.
• breadcrumb: The breadcrumb string showing the hierarchical path from the root to the child category.

The CTE consists of two parts:

• The initial part retrieves direct parent-child relationships between categories and creates the breadcrumb for those relationships.
• The recursive part continues to traverse the hierarchy to find children of children and builds the breadcrumbs accordingly.

6. Selecting and displaying the results: The final query uses the recursive CTE to select and display the results. The output will show all categories with their parent categories and the breadcrumb paths.

Note: The current structure does not account for circular references in the category hierarchy, which might lead to infinite loops in the recursive CTE. It is essential to ensure that the data is set up correctly to avoid such issues.

              
                CREATE TABLE categories (
    id int IDENTITY(1,1) not null,
    created_date datetime not null DEFAULT GETDATE(),
    updated_date datetime not null DEFAULT GETDATE(),
    title nvarchar(max) NOT NULL,
    CONSTRAINT PK_categories_id PRIMARY KEY CLUSTERED (id),
);


CREATE TABLE childcategories (
  parent_id int,
  child_id int,
 CONSTRAINT PK_childcategories PRIMARY KEY NONCLUSTERED ([parent_id],[child_id]),
   CONSTRAINT FK_childcategories_parent_id FOREIGN KEY (parent_id) REFERENCES  categories(id),
   CONSTRAINT FK_childcategories_child_id FOREIGN KEY (child_id) REFERENCES  categories(id),
);


insert into categories(title)
values ('hardware'), ('cpu'), ('gpu') , ('intel'), ('amd'), ('nvidia') , ('geforce')



insert into childcategories(parent_id,child_id)
values (1,2), (1,3),(2,4), (2,5),(3,4), (3,5), (3,6), (6,7)

with cte as (
		select c.id,c.title as parent_title, 1 as [level],cat.title as child_title,
		case when c.title<>cat.title then c.title + ' > ' + cat.title else c.title end as breadcrumb
		from categories as c
		left join childcategories as ch on ch.parent_id=c.id
		left join categories as cat on cat.id=ch.parent_id
		where ch.parent_id is not null
		union all
		select  cat.id,cte.parent_title as parent_title, cte.[level] + 1,cat.title as child_title,
			case when cte.parent_title<>cat.title and cte.parent_title<>par.title then cte.parent_title + ' > ' +par.title + ' > ' +  cat.title else par.title + ' > ' +cat.title end as breadcrumb
		from cte
			inner join childcategories as ch on ch.parent_id=cte.id
			inner join categories as cat on cat.id=ch.child_id
			inner join categories as par on par.id=cte.id
)

select distinct * from cte  order by id, [level];