SQL query for Join statement

I have 3 tables products, pricingGroup and productPricing. I want to show all products even it has no service pricing and set its status/IsActive to false.

What query I have now

SELECT product.productId, 
       products.ProductCode, 
       product.ProductDetails, 
       product.ProductDescription,
       (CASE WHEN prgrp.pricingGroupId IS NOT NULL && product.IsActive = 1 THEN 1 
       ELSE 0 END) IsActive, 
       prgrp.PricingName,
       pricing.amount 
FROM Products product
LEFT OUTER JOIN productPricing AS pricing ON product.productId = pricing.productId OR pricing.productId IS NULLL
LEFT OUTER JOIN pricingGroup as prgrp on true

What I want to Get

ProductId    ProductCode  ProductDetails  ProductDescrition  IsActive   PricingGroupName  Amount
1            PRCD1        detail1         Descrition1        1          MERCHANT          20
1            PRCD1        detail1         Descrition1        0          Consumer          20
1            PRCD1        detail1         Descrition1        0          OTHERS            20
2            PRCD2        detail2         Descrition2        1          Consumer          25
2            PRCD2        detail2         Descrition2        0          MERCHANT          25
2            PRCD2        detail2         Descrition2        0          OTHERS            25
3            PRCD3        detail3         Descrition3        0          Consumer          0
3            PRCD3        detail3         Descrition3        0          MERCHANT          0
3            PRCD3        detail3         Descrition3        0          OTHERS            0

Products Table

ProductId    ProductCode  ProductDetails  ProductDescrition  IsActive
1            PRCD1        detail1         Descrition1        1
2            PRCD2        detail2         Descrition2        1
3            PRCD3        detail3         Descrition3        0

---

ProductPricing Table

ProductPricingId    PricingGroup  ProductId  Amount  Fee
1                   1             1          20      5
2                   1             2          25      5

---

PricingGroup Table

PricingGroupId    PricingName  
1                 Merchant       
2                 Consumer
3                 Others     

---

Answer

• You are missing last join clause pricing.PricingGroup = prgrp.PricingGroupId.
• You don’t need OR products.productId IS NULL because that is primary key of Products table and will not be null.

SELECT product.ProductId, 
    product.ProductCode, 
    product.ProductDetails, 
    product.ProductDescription,
    IF(pricing.ProductId = product.ProductId && product.IsActive = 1, 1, 0) IsActive, 
    prgrp.PricingName, 
    pricing.amount 
FROM Products product
JOIN pricingGroup as prgrp
LEFT JOIN productPricing AS pricing ON pricing.PricingGroup = prgrp.PricingGroupId && pricing.productId = product.productId;