CODEWITHSUNDEEP
phpsqlmysql
Schodemeiss
Schodemeiss

Reputation: 1174

How can I shorten this MySQL Query?

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

Upvotes: 0

Views: 1038

Answers (4)

tgandrews
tgandrews

Reputation: 12670

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.

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166486

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'

Upvotes: 4

Jammin
Jammin

Reputation: 3100

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.

Upvotes: 0

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14341

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.

Upvotes: 6

Related Questions