SQL Query Producing No Results

Question

Hello,

I am trying to make this query work:

SELECT Stock.*,
       StockFeatures.Features,
       StockDescriptions.Detailed,
       StockDescriptions.Technical,
       PRD1.RuleValue as Price, 
       PRD2.RuleValue as WasPrice,
       PRD2.RuleValue-PRD1.RuleValue as Save,
       PRD1.Quantity
FROM 
    StockFeatures, Stock INNER JOIN
    PriceRuleDetail PRD1 ON PRD1.Sku = Stock.Sku
        AND PRD1.PriceRule = 'RG' LEFT JOIN
    PriceRuleDetail PRD2 ON PRD2.Sku = Stock.Sku
        AND PRD2.PriceRule = 'RRP' LEFT JOIN
    StockDescriptions ON StockDescriptions.Sku = Stock.Sku
WHERE Stock.GeneralStkStatus < 3
AND Stock.Sku = '11044'
AND StockFeatures.Sku = Stock.Sku
ORDER BY PRD1.Quantity ASC

It returns no results whenever there isn't a StockFeatures.Features row in the StockFeatures table - which there frequently wont be. How do I make it just come up with NULL values whenever there isn't anything in that table???

The table has the columns Sku and Features (Sku should be linked with the Stock.Sku column).

Any help would be appreciated.

Thanks in advance.

Answer 1

You need to LEFT JOIN to StockFeatures.

For example: (Untested)

SELECT Stock.*,
       StockFeatures.Features,
       StockDescriptions.Detailed,
       StockDescriptions.Technical,
       PRD1.RuleValue as Price, 
       PRD2.RuleValue as WasPrice,
       PRD2.RuleValue-PRD1.RuleValue as Save,
       PRD1.Quantity
FROM 
    Stock LEFT JOIN 
    StockFeatures ON Stock.Sku = StockFeatures.Sku INNER JOIN
    PriceRuleDetail PRD1 ON PRD1.Sku = Stock.Sku
        AND PRD1.PriceRule = 'RG' LEFT JOIN
    PriceRuleDetail PRD2 ON PRD2.Sku = Stock.Sku
        AND PRD2.PriceRule = 'RRP' LEFT JOIN
    StockDescriptions ON StockDescriptions.Sku = Stock.Sku
WHERE Stock.GeneralStkStatus < 3
AND Stock.Sku = '11044'
ORDER BY PRD1.Quantity ASC