CODEWITHSUNDEEP
phpsqlmysql
Schodemeiss
Schodemeiss

Reputation: 1174

Perform this query when something doesn't exist in another table

This is a really tricky question to ask as its very hard to explain what I'm after. Here goes though!

SELECT
    Stock.*,
    REPLACE (Description, '&', '&') as Description,
    PRD1.RuleValue AS Price, 
    PRD2.RuleValue AS WasPrice,
    PRD2.RuleValue-PRD1.RuleValue AS Save
FROM 
    Stock INNER JOIN
    StockCategoryMemberList ON StockCategoryMemberList.Sku = Stock.Sku LEFT JOIN 
    PriceRuleDetail PRD1 ON PRD1.Sku = Stock.Sku
        AND PRD1.PriceRule = 'RG'
        AND PRD1.Quantity = 1 LEFT JOIN
    PriceRuleDetail PRD2 ON PRD2.Sku = Stock.Sku
        AND PRD2.PriceRule = 'RRP'
        AND PRD2.Quantity = 1
WHERE StockCategoryMemberList.CategoryCode = 'FIRE'
AND Stock.GeneralStkStatus < 3
ORDER BY StockCategoryMemberList.Order ASC

This query works fine and produces all the correct data. I also have another table though called "StockMatrixDetail". I would like to remove any SKUs from the above query when the Sku exists in the StockMatrixDetail.Sku column. How can I mend this above query to include that???

So, the pseudo question is basically:

Perform the above query, but don't include any Skus that are in the StockMatrixDetail table, in the Column Sku.

Any help with this would be appreciated.

Kind Regards,

Andy

Upvotes: 1

Views: 237

Answers (1)

Nick Craver
Nick Craver

Reputation: 630627

I've always done this via a NOT EXISTS clause...extremely fast, even more so if StockMatrixDetail.Sku is indexed...it doesn't even hit the table.

SELECT
    Stock.*,
    REPLACE (Description, '&', '&amp;') as Description,
    PRD1.RuleValue AS Price, 
    PRD2.RuleValue AS WasPrice,
    PRD2.RuleValue-PRD1.RuleValue AS Save
FROM 
    Stock INNER JOIN
    StockCategoryMemberList ON StockCategoryMemberList.Sku = Stock.Sku LEFT JOIN 
    PriceRuleDetail PRD1 ON PRD1.Sku = Stock.Sku
        AND PRD1.PriceRule = 'RG'
        AND PRD1.Quantity = 1 LEFT JOIN
    PriceRuleDetail PRD2 ON PRD2.Sku = Stock.Sku
        AND PRD2.PriceRule = 'RRP'
        AND PRD2.Quantity = 1
WHERE StockCategoryMemberList.CategoryCode = 'FIRE'
AND NOT EXISTS (SELECT 1
                  FROM StockMatrixDetail smd
                  WHERE smd.Sku = Stock.Sku)
AND Stock.GeneralStkStatus < 3
ORDER BY StockCategoryMemberList.Order ASC

Upvotes: 4

Related Questions