Reputation: 308
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 :)
Upvotes: 1
Views: 558
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