Run 2nd query within main query and calculate a new price

Databases
1

Hi,

I have a table of products with one of the fields used as the price of the product.

I need to query a second table to see if a sale campaign is running (for each product) and if so deduct the sale discount from the product price.

Products table: tblproducts
(some of) the fields:

  • prodId (unique ID)
  • prodPrice (price fields - DECIMAL 8,2)
  • prodEnabled (Y/N depending on whether the product is enabled or not)
  • prodSaleId (the ID of sale campaing, will be 0 (zero) if no sale has been selected.)

Currently the recordset is sorted by the price (ASC).

My select statement looks a bit like this: (the(prodPrice >= ? AND prodPrice <= ?) query is returning only products that are within a certain price band based on a querystring value)

"SELECT prodId, prodTitle, prodPrice, prodSaleId, salenabled, salexpiry, saldiscount FROM lfstlwrhsdb.tblproducts LEFT JOIN lfstlwrhsdb.tblsalecampaigns ON prodSaleId = salId WHERE prodEnabled = 'Y' AND (prodPrice >= ? AND prodPrice <= ?) ORDER BY prodPrice ASC"

You’ll notice that in the SELECT statement above there is reference to another table which holds information about sale campaigns.

Sales table: tblsalecampaigns
(some of) the fields:

  • salId (unique ID)
  • salexpiry (expiry DATE of the sale - if the sate is past the sale does not run)
  • salenabled (Y/N depending on whether the sale is enabled/running or not)
  • saldiscount (sale discount percentage, for instance a value of 10 means a discount of 10% will be deducted from the product price - DECIMAL 8,2)

So the query looks at the value of prodSaleId (for each product) and checks to see if there is a sale with that ID number and if there is the “salenabled, salexpiry, saldiscount” fields are outputted to the recordset.

At the moment I query the database with my select statement and then display my recordset using a WHILE/WEND (Classic ASP) loop. For each loop I check to see if a sale is running (salenabled) and has not expired (salexpiry) and then using the saldiscount calculate how much to take off the product price and create a new variable for the sale price - the sale isn’t running then the price is left alone.

This all works ok however the “order” of the products is not correct. The order is based on the product price and it currently doesn’t take into account any reduction to the price that will occur if a sale is running - so I might get this scenario:

product A = £10.00
product B = £12.00
product C = £9.00 (was £18.00 but is part of a 50% off sale)
product D = £20.00

Also the (prodPrice >= ? AND prodPrice <= ?) query is returning only products that are within a certain price band based on the actual product price, again not taking into account whether the sale is on or not.

What I need to do is run the “is sale running and what discount” query and create the actual price in the SELECT statement and I don’t know how to do that.

The query needs to check the value of prodSaleId and see if that ID matches salId in the tblsalecampaigns table. If it does it needs to check whether the sale is enabled (salenabled = ‘Y’) and if the sale has not expired (salexpiry > CurDate()) - if it is running and not expired then we need to take the sale discount (saldiscount) and calculate the new price - in ASP I calculate the sale price like this:

SalePrice = (Cdbl(prodPrice) * Cdbl(CDbl((100 - Cdbl(saldiscount))) / 100))

I then need this new price to be used for the ORDER BY and for the (prodPrice >= ? AND prodPrice <= ?) price band query.

Can this sort of query be done within a SELECT statement?

I hope someone can help me.

2

Adding the checks on salenabled and salexpiry in the LEFT JOIN’s ON clause gives you the possibility to calculate the final price in the query using COALESCE. I’m not sure what I did in the WHERE clause will work (didn’t test it), but who knows :slight_smile:


SELECT 
    prodId
  , prodTitle
  , prodPrice
  , prodSaleId
  , saldiscount 
  , prodPrice - COALESCE(saldiscount, 0) AS finalPrice
FROM lfstlwrhsdb.tblproducts 
LEFT JOIN lfstlwrhsdb.tblsalecampaigns 
ON prodSaleId = salId 
AND salenabled = 'Y'
AND salexpiry >= CURDATE()
WHERE prodEnabled = 'Y' 
AND prodPrice - COALESCE(saldidiscount, 0) >= ? 
AND prodPrice - COALESCE(saldidiscount, 0) <= ?
ORDER BY finalPrice ASC
3

Hi Guido,

Thank you very much for your reply. Your solution is much cleaner than mine which was to use an IF clause in the SELECT statement:

SELECT
prodId,
prodTitle,
prodPrice,
prodSaleId,
saldiscount,
IF(salenabled = ‘Y’ AND salexpiry > CurDate(), (prodPrice * ((100 - saldiscount) / 100)), prodPrice) AS actualprice
FROM lfstlwrhsdb.tblproducts
LEFT JOIN lfstlwrhsdb.tblsalecampaigns ON prodSaleId = salId
WHERE prodEnabled = ‘Y’
AND ((IF(salenabled = ‘Y’ AND salexpiry > CurDate(), (prodPrice * ((100 - saldiscount) / 100)), prodPrice)) >= ? AND (IF(salenabled = ‘Y’ AND salexpiry > CurDate(), (prodPrice * ((100 - saldiscount) / 100)), prodPrice)) <= ?)
ORDER BY actualprice

The only bit I needed to change was the value/field referenced in the COALESCE as the saldiscount is a percentage off rather than an amount off so I changed it to:

COALESCE(prodPrice * (saldiscount / 100), 0) AS actualprice

Thanks again for your help.