SQL LEFT JOIN to many categories-CodePudding

Suppose the following easy scenario, where a product row gets connected to one primary category, subcategory, and sub-subcategory.

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));

INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1', '10', '100'),
(2, 'NIKE CORTEZ', '1', '12', '104'),
(3, 'ADIDAS PANTS', '2', '27', '238'),
(4, 'PUMA REVOLUTION 5', '3', '35', '374'),
(5, 'SALOMON SHELTER CS', '4', '15', '135'),
(6, 'NIKE EBERNON LOW', '2', '14', '157');

DECLARE @CATS TABLE (ID int, DESCR varchar(100));

INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');

DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');

DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');


SELECT prod.ID,
    prod.DESCRIPTION,
    CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
FROM @PRODUCTS AS prod
LEFT JOIN @CATS AS cat1 ON cat1.ID = prod.CAT
LEFT JOIN @SUBCATS AS cat2 ON cat2.ID = prod.SUBCAT
LEFT JOIN @SUBSUBCATS AS cat3 ON cat3.ID = prod.SUBSUBCAT;

Now suppose that the foreign keys on @PRODUCTS table aren't just indices to their respective tables. They are comma-separated indices to more than one categories, subcategories, and sub-subcategories like here.

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));

INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6', '27, 35', '238, 374');

DECLARE @CATS TABLE (ID int, DESCR varchar(100));

INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');

DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');

DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');


SELECT prod.ID,
    prod.DESCRIPTION
    --CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
FROM @PRODUCTS AS prod
--LEFT JOIN @CATS AS cat1 ON cat1.ID = prod.CAT
--LEFT JOIN @SUBCATS AS cat2 ON cat2.ID = prod.SUBCAT
--LEFT JOIN @SUBSUBCATS AS cat3 ON cat3.ID = prod.SUBSUBCAT;

In this case I want to achieve the following:

Be able to retrieve the respective names of the cats, subcats, sub-subcats, ie. for cats '1, 2' be able to retrieve their names (I tried LEFT JOIN @CATS AS cat1 ON cat1.ID IN prod.CAT but it doesn't work)
Create triplets of the corresponding cats, subcats, sub-subcats, ie. for

cats '1, 2'
subcats '12, 17'
sub-subcats '239, 372'

(after retrieving the appropriate names) create pipe-separated category routes like name of cat 1 > name of subcat 12 > name of sub-subcat 239 | name of cat 2 > name of subcat 17 > name of sub-subcat 372

So, for a row like (1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),

I would like to get the following result

ID	DESCRIPTION	CATEGORIES
1	NIKE MILLENIUM	MEN > FOOTWEAR > RUNNING @ WOMEN > OUTERWEAR > FLEECE (I had to use @ as the delimiter of the two triplets because pipe messed with the table's columns)

In case the user stupidly stores more cat IDs than subcat IDs, or sub-subcat IDs, the query should just match the ones that have a corresponding position match, ie for

cats '1, 2'
subcats '12'
sub-subcats '239, 372'

it should just create one triplet, like name of 1 > name of 12 > name of 239

CodePudding user response：

STRING_SPLIT() does not promise to return the values in a specific order, so it won't work in this case as ordinal position matters.

Use OPENJSON() split the string into separate rows to ensure the values are returned in the same order.
OPENJSON() also returns a key field, so you can join on the row number within each grouping. You'll want an INNER JOIN since your requirement is that all values in that "column" must exist.
Use STUFF() to assemble the various cat>subcat>subsubcat values.

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));

INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6, 1', '27, 35, 10', '238, 374, 100'),
(4, 'JOE THE PLUMBER JEANS', '1, 5', '27', '238, 374');

DECLARE @CATS TABLE (ID int, DESCR varchar(100));

INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');

DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');

DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));

INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');

;
with prod as (
    SELECT p.ID,
        p.DESCRIPTION
        --CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
        , c.value as CatId
        , c.[key] as CatKey
        , sc.value as SubCatId
        , sc.[key] as SubCatKey
        , ssc.value as SubSubCatId
        , ssc.[key] as SubSubCatKey
    FROM @PRODUCTS p
      cross apply OPENJSON(CONCAT('["', REPLACE(cat, ', ', '","'), '"]')) c
      cross apply OPENJSON(CONCAT('["', REPLACE(subcat, ', ', '","'), '"]')) sc
      cross apply OPENJSON(CONCAT('["', REPLACE(subsubcat, ', ', '","'), '"]')) ssc
    where c.[key] = sc.[key]
      and c.[key] = ssc.[key]
)
, a as (
    select p.ID
    , p.DESCRIPTION
    , c.DESCR   ' > '   sc.DESCR   ' > '   ssc.DESCR as CATEGORIES
    , p.CatKey
    from prod p
      inner join @CATS c on c.ID = p.CatId
      inner join @SUBCATS sc on sc.ID = p.SubCatId
      inner join @SUBSUBCATS ssc on ssc.ID = p.SubSubCatId
)

select DISTINCT ID
, DESCRIPTION
, replace(STUFF((SELECT distinct ' | '   a2.CATEGORIES
            from a a2
            where a.ID = a2.ID
            FOR XML PATH(''))
        ,1,2,''), '&gt;', '>') CATEGORIES
from a

CodePudding user response：

In order for this to work you will have to return the ProductID and each CategoryID in multiple ctes so you can split every level. As I am posting this I see you don't have string_split. Not a huge deal, there are plenty of pre-2016 splitters out there.

Here is how this might look using string_split. You can switch out that table valued function for another one easily. Here is an article with a number of alternatives. https://sqlperformance.com/2021/09/t-sql-queries/split-strings

with ProdCategory as
(
    select p.ID
        , Category = try_convert(int, x.value)
    from @PRODUCTS p
    cross apply string_split(p.CAT, ',') x
)
, ProdSubCat as
(
    select p.ID
        , SubCat = try_convert(int, x.value)
    from @PRODUCTS p
    cross apply string_split(p.SUBCAT, ',') x
)
, ProdSubSubCat as
(
    select p.ID
        , SubSubCat = try_convert(int, x.value)
    from @PRODUCTS p
    cross apply string_split(p.SUBSUBCAT, ',') x
)

SELECT prod.ID
    , prod.DESCRIPTION
    , CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
FROM @PRODUCTS AS prod
left join ProdCategory cat on cat.ID = prod.ID
left join @CATS as cat1 on cat1.ID = cat.Category
left join ProdSubCat psc on psc.ID = prod.ID
left join @SUBCATS AS cat2 on cat2.ID = psc.SubCat
left join ProdSubSubCat pssc on pssc.ID = prod.ID
left join @SUBSUBCATS AS cat3 on cat3.ID = pssc.SubSubCat