Select query from another table

Question

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.

Do you only ever have 3 levels max? Never 4? If so just use 3 left joins. — Dale K, Mar 09 '21 at 04:00
Is there only one hierarchy (a/b/c) that applies to all stores? If so, it doesn't matter what `category_id` is assigned to a `store_id`, `lv1` will always be `'a'`, `lv2` `'b'` and `lv3` `'c'`. — HABO, Mar 09 '21 at 04:17
There is only 3 level but each level has more than one value, can be `lv1` `aa`. My problem is getting all categories that relate to each store. I'm trying to combine left join 3 times to find the highest level of `category_id`. And according to that combine table I will get all `subcategories`. I forgot to mention that a store is not only reference to the same level of category — Young, Mar 09 '21 at 06:30

score 1 · Accepted Answer · answered Mar 09 '21 at 06:18

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

Select query from another table

1 Answers1