CODEWITHSUNDEEP
sqlsql-servert-sql
Young
Young

Reputation: 107

Select query from another table

I have two tables:

CREATE TABLE store_category (
    store_id    int  NOT NULL PRIMARY KEY,
    category_id char NOT NULL
)

CREATE TABLE category (
    category_id char NOT NULL PRIMARY KEY,
    parent_id   char     NULL,
    name        char NOT NULL
)
store_id category_id
1 1a
2 2b
3 3c

category table: this table has 3 levels of category. The highest level of category has NULL in parent_id:

category_id parent_id name
1a NULL a
2b 1a b
3c 2b c

I want to get all related category name of store category.

Expected query result:

store_id category_id lv1 lv2 lv3
1 1a a b c
2 2b a b c
3 3c a b c

I have an idea, I will get all category names that relate to each category_id in category table into temp table and then join temp table with store_category table. I wonder if that is a good idea.

Upvotes: 0

Views: 124

Answers (1)

Dai
Dai

Reputation: 155145

I have an idea, I will get all category names that relate to each category_id in category table into temp table and then join temp table with store_category table. I wonder if that is a good idea.

It is a good idea - except you don't need a temporary table - nor a loop. All you need is a Recursive (self-referential) CTE. CTEs are how you select data hierarchies and other graph structures.

However I don't think it's a good idea to PIVOT (or more specifically, UNPIVOT) your data into those lv1, lv2, and lv3 columns - I know your business rules say there will only be 3 levels of depth, but your data-model does allow more than 3 levels of depth (unless you have a CHECK CONSTRAINT with a UDF that uses the same style of CTE query to restrict maximum depth). When you have a dimension of data that varies with a query (like this one!) then you should return them as rows and then format them for display to end-users in your application code, not directly in SQL.

As for the hierarchical query: Read this: CTE Recursion to get tree hierarchy

Something like this:

DECLARE @getThisCategoryId char(2) = '3c';

WITH my_cte AS (
    
    SELECT
        c.category_id,
        c.parent_id,
        c.name,
        1 AS depth
    FROM
        category AS c
    WHERE
        c.category_id = @getThisCategoryId 

    UNION ALL
    
    SELECT
        other_categories.category_id,
        other_categories.parent_id,
        other_categories.name,
        collected_so_far.depth + 1 AS depth
    FROM
        category AS other_categories
        INNER JOIN my_cte AS collected_so_far ON
            other_categories.category_id = collected_so_far.parent_id    
)
SELECT
    *
FROM
    my_cte
    INNER JOIN store_category AS sc ON my_cte = category_id
ORDER BY
    sc.store_id

Upvotes: 1

Related Questions