How can I shorten this MySQL Query?

Question

Here there,

Is there anyway of shortening this MySQL query at all??? It needs to grab two diffrent rows from the PriceRuleDetail table from the column called RuleValue based on it's price rule, but using an Alias, have them returned in the same row. It's using subqueries inside the select statement, which I assume is right, but there may be an easier way?

The below does work correctly and i'm reasonably happy with it, but I was wondering if there was a way of making this shorter???

SELECT Stock.*, 
    (SELECT PriceRuleDetail.RuleValue
    FROM PriceRuleDetail
    WHERE PriceRuleDetail.Sku = Stock.Sku
    AND PriceRuleDetail.PriceRule = 'RG'
    AND PriceRuleDetail.Quantity = 1) as Price,
    (SELECT PriceRuleDetail.RuleValue
    FROM PriceRuleDetail
    WHERE PriceRuleDetail.Sku = Stock.Sku
    AND PriceRuleDetail.PriceRule = 'RRP'
    AND PriceRuleDetail.Quantity = 1) as WasPrice
FROM Stock, StockCategoryMemberList
WHERE StockCategoryMemberList.Sku = Stock.Sku
AND StockCategoryMemberList.CategoryCode = 'FIRE'

Thanks in advance.

Andy

Answer 1

Make it a single query, with joins:

SELECT
   Stock.*, 
   PRD1.RuleValue as Price, 
   PRD2.RuleValue as WasPrice
FROM 
   Stock, 
   StockCategoryMemberList, 
   PriceRuleDetail PRD1, 
   PriceRuleDetail PRD2
WHERE
StockCategoryMemberList.Sku = Stock.Sku
AND StockCategoryMemberList.CategoryCode = 'FIRE'
AND PRD1.Sku = Stock.Sku
    AND PRD1.PriceRule = 'RG'
    AND PRD1.Quantity = 1
AND
    PRD2.Sku = Stock.Sku
    AND PRD2.PriceRule = 'RRP'
    AND PRD2.Quantity = 1

I made the same assumption you made (that for one stock record there is only one price and only one WasPrice). If it is not the case... the query will not be ok. Nor yours.

Answer 2

I would suggest actually lengthening it and not using Stock.*, but using each column name. When you use * it must query the table to get the column names, and is a wee bit slower.

Answer 3

You can use left joins to accomplish this

 SELECT Stock.*, 
     PRD1.RuleValue as Price, 
     PRD2.RuleValue as WasPrice
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'

Answer 4

select s.*, prd1.rulevalue as price, prd2.rulevalue as wasprice
from
  stock s
  inner join stockcategorymemberlist scm
      on s.sku = scm.sku
  inner join priceruledetail prd1
      on prd1.sku = s.sku and prd1.pricerule='RG' and prd1.quantity=1
  inner join priceruledetail prd2
      on prd2.sku = s.sku and prd2.pricerule='RRP' and prd2.quantity=1
where
  s.categorycode='FIRE'

This will also be quicker as you won't have to perform each of the sub-selects for each row in the table.