SQL Server multiple table JOIN

Question

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:

enter image description here

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.

please put code to create tables and some sample rows. chenqui. — Erran Morad
– Erran Morad, Commented Aug 2, 2014 at 23:54
Then, put only those columns which you are interested in. Figure out a way to create a sample case. — Erran Morad
– Erran Morad, Commented Aug 2, 2014 at 23:56

Marcel N. · Accepted Answer · 2014-08-02 23:55:20Z

1

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)

answered Aug 2, 2014 at 23:55

Marcel N.

14k5 gold badges50 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server multiple table JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related