How to show all products which not included in another table. by STRING_AGG

Question

I am currently working on part of a project which is about products and variants.

Product Table

productID   productName
-------------------------
1           Perfume X
2           Perfume Y

Variants Table

variantID   variantName productID
1           color       1
2           volume      1

VariantValue Table

variantValueID  variantValue    variantID
1               Red             1
2               Blue            1
3               Green           1
4               100ML           2
5               50ML            2

ProductVariant Table

productVariantID    productID   sku price
1                   1           111 50.00
2                   1           222 30.00
4                   1           333 15.00
5                   2           444 10.95

ProductDetail Table

ProductDetailID productVariantID    variantValueID
1               1                   1
2               1                   4
3               2                   1
4               2                   5
5               4                   2
6               4                   5

relationship

and when I call this query ..

SELECT  p.productName + ' ' + STRING_AGG(vv.variantValue,' ') product,pv.price
FROM
    product p
    JOIN ProductVariant pv ON pv.productID = p.productID
    JOIN ProductDetail pd ON pv.productVariantID = pd.productVariantID
    join VariantValue vv ON pd.variantValueID = vv.variantValueID
GROUP BY pd.productVariantID, p.productName,pv.price

I get the following result ..

product             price
Perfume X Red 100ML 50.00
Perfume X Red 50ML  30.00
Perfume X Blue 50ML 15.00

So far so good, it shows all products with the prices but when I insert a new product without variants (simple product) it does not appeared. In my case Perfume Y does not have any variants.

I tried using right and full join but in vain. How I show all products including those product that have no variants.

Answer 1

I think you just want a left join:

SELECT p.productName + COALESCE(' ' + STRING_AGG(vv.variantValue,' '), '') as product,
       pv.price
FROM product p LEFT JOIN
     ProductVariant pv
     ON pv.productID = p.productID LEFT JOIN
     ProductDetail pd
     ON pv.productVariantID = pd.productVariantID LEFT JOIN
     VariantValue vv
     ON pd.variantValueID = vv.variantValueID
GROUP BY p.productName, pv.price