SQL Query Producing No Results

Question

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.

I'm sorry, but if there is no StockFeature (as you said, it could be null), with what row in Stock.Sku you want to join it????? or you just want a full row of nulls? — gbianchi
– gbianchi, Commented Jan 15, 2010 at 15:25

SLaks · Accepted Answer · 2010-01-15 15:21:51Z

7

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

answered Jan 15, 2010 at 15:21

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Schodemeiss Over a year ago

Can you give me an example please??? I've tried doing it, but it seems to give an error every time! Pretty new to SQL :(. Cheers.

Collectives™ on Stack Overflow

SQL Query Producing No Results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related