Mysql joining multiple tables with hierarchical data

Question

I am building a database for an inventory management application and I have to maintain products with multiple hierarchies of category. I have the following database model :

I want to retrieve the products, description, units of measure and the categories, for which I have used the following query :

SELECT P.ID
    ,P.wrin
    ,P.description AS productDescription
    ,CT.pdtCat AS productCategory
    ,CONCAT(UN.description,' - ',UN2.description,' - ',UN3.description) AS unitDecomposition
    FROM product P 
    -- JOIN to product_category table 
    JOIN (
         SELECT PC3.ID as catID
         ,CONCAT(PC1.category,' - ',PC2.category,' - ',PC3.category) as pdtCat 
         FROM   product_category AS PC1 
                 LEFT JOIN product_category AS PC2 ON PC2.parentid = PC1.id 
                 LEFT JOIN product_category AS PC3 ON PC3.parentid = PC2.id 
         WHERE  PC1.parentid IS NULL            
    ) CT ON CT.catID = P.categoryId                 
    JOIN unit UN ON P.primaryUOM = UN.ID 
    JOIN unit UN2 ON P.secondaryUOM = UN2.ID 
    JOIN unit UN3 ON P.tertiaryUOM = UN3.ID

The output from this query is :

My query is giving the intended results but seems to go sideways if my product doesn't have 3 levels of categories. The product doesn't appear in the results. Please help :)

Raphael Müllner · Accepted Answer · 2017-09-27 11:12:53Z

1

In your sub-select you are joining from the "top" category to the bottom category and select the id of the bottom one. If there is no level 3 category, there are no results.

Try something like this:

SELECT P.ID
    ,P.wrin
    ,P.description AS productDescription
    ,CT.pdtCat AS productCategory
    ,CONCAT(UN.description,' - ',UN2.description,' - ',UN3.description) AS unitDecomposition
    FROM product P 
    -- JOIN to product_category table 
    JOIN (
         SELECT PC3.ID as catID
         ,CONCAT(PC1.category,' - ',COALESCE(PC2.category, ''),' - ',COALESCE(PC3.category, '')) as pdtCat 
         FROM   product_category AS PC3 
                 LEFT JOIN product_category AS PC2 ON PC3.parentid = PC2.id
                 LEFT JOIN product_category AS PC1 ON PC2.parentid= PC1.id        
    ) CT ON CT.catID = P.categoryId                 
    JOIN unit UN ON P.primaryUOM = UN.ID 
    JOIN unit UN2 ON P.secondaryUOM = UN2.ID 
    JOIN unit UN3 ON P.tertiaryUOM = UN3.ID

EDIT: My query will produce some ' - - category' results for the product category. Due to the fact that my strength is in MSSQL, that's an exercise for you to make it look more pretty ;)

edited Sep 27, 2017 at 11:12

answered Sep 27, 2017 at 10:40

Raphael Müllner

4383 silver badges7 bronze badges

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

6 Comments

Ashwin Jugurnauth Over a year ago

Did you forget to add something ? ^^ It seems to be the same as my original query...

Raphael Müllner Over a year ago

No, I have changed the join order in the sub select.

Ashwin Jugurnauth Over a year ago

It returns a null value for products with only one level of category :/

Raphael Müllner Over a year ago

I updated my answer, please try it with the new one.

Ashwin Jugurnauth Over a year ago

Thank you my good Sir :) Works like a charm. There are ugly "-" for certain products but that I can fix :)

|

Collectives™ on Stack Overflow

Mysql joining multiple tables with hierarchical data

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related