问题描述:

I have 3 tables

  • Table 1 [StockItem] contains information about an item, I need only the Id from this table

  • Table 2 [Categories] contains the categories and subcategories with their names and URL slug

  • Table 3 [ItemCategories] contains the categories for each item and has only two columns, CategoryId and StockItemId

I have this query which now returns the category id and combined slug as category/subcategory...

WITH categoryPath(Id, Slug)

AS

(

SELECT

Id, Slug

FROM

Categories

WHERE

ParentCategoryId IS NULL

UNION ALL

SELECT

Categories.Id

,CAST(categoryPath.Slug + '/' + categories.Slug AS NVARCHAR(150))

FROM

Categories

JOIN

categoryPath ON Categories.ParentCategoryId = categoryPath.Id

)

SELECT *

FROM ItemCategories

JOIN categoryPath ON ItemCategories.StockId = categoryPath.Id

WHERE ItemCategories.StockId = 5

The result of this query looks like this:

What I want is to add to the result the Category Name from the [Categories] table which have the column [CategoryName], but I don't know how to add another JOIN to this already complex query.

网友答案:

If the column is indeed in that table, then you can use this SELECT in the CTE.

You don't need another join, you just need the name column to be part of the categoryPath CTE.

SELECT Id,
       ,Slug
       ,CategoryName 
     FROM Categories
     WHERE ParentCategoryId IS NULL
UNION ALL
SELECT Categories.Id
     ,CAST(categoryPath.Slug + '/' + categories.Slug AS NVARCHAR(150))  
     ,CategoryName
     FROM Categories

Don't forget to update the CTE definition with:

WITH categoryPath(Id,Slug,CategoryName)
相关阅读:
Top