Ashwin Jugurnauth
Ashwin Jugurnauth

Reputation: 308

Mysql joining multiple tables with hierarchical data

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 :

enter image description here

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 :

enter image description here 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 :)

Upvotes: 1

Views: 558

Answers (1)

Raphael Müllner
Raphael Müllner

Reputation: 438

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 ;)

Upvotes: 1

Related Questions