how to approach this specific query

Question

Hi,

I have a rather complex logical query I'm trying to execute. Essentially there are a series of related tables. I am having difficulty figuring out a good way to approach it.

Tables:

Transaction -
T_id

Discount -
D_id
item_id
Type (percentage or basic value)
value

Transaction_Discount -
T_id
D_id

Item -
item_id
price

EDIT: additional table
Purchase
T_id
item_id

I am attempting to pull the price adjusted after the discount is applied. I would like to avoid adding another column with the adjusted price to the database... as it is possible to calculate the adjusted price for each transaction from the data already entered (so it'd be redundant to store this data).

This is the basic logic:

if(this transaction had a discount applied)  
  //apply the discount and return the adjusted price  
else(no discount applied)  
  //pull price

It is possible to do this in several separate queries using PHP logic.

//1st step - create an array of bool's: true = discount_used/false = no_discount
//2nd step - if(discount_used) return price adjusted to discount
//3rd step - if(no_discount) return price
//4th step - combine the two different arrays

this is clearly very bulky. It seems like there HAS to be a better way to do this that is likely much more efficient.

It is my understanding that you can perform queries that contain logic in mysql, would that help here?

Answer 1

As long as you can create a query (probably a left join) which has the original prices, and if it exists, the discounted price, then you can use an IF statement to select the discounted price if it exists.

http://dev.mysql.com/doc/refman/5.1/en/if-statement.html

Answer 2

Derek, I would suggest a stored function. After creating a function you could simply do this:

select item_id, price_after_discount(item_id) from item where ....

Answer 3

I don't think you can. For a particular T_id, if there is no discount, you have no way of joining to the item_id, since the only tables with item_id are the Item table and the Discount table.

You are going to need to change how your tables are set up if you want to have transactions without discounts.

You might try something like:

Item table:
  item_id
  price

Transaction table:
  T_id
  item_id
  D_id

Discount table:
  D_id
  type
  value

Unless you want to apply a particular discount to every instance of a particular item. Then you'll want to do something like:

Item table:
  item_id
  price
  D_id (can be null)

Transaction table:
  T_id
  item_id

Discount table:
  D_id
  type
  value

Answer 4

Another approach - perform the two conditions as separate queries and union the results, e.g. something along the lines of (adjust for your tables of course)

SELECT unadjustedPrice as Price FROM Transaction WHERE noDiscount = true
UNION ALL
SELECT unadjustedPrice - discount as Price From Transaction WHERE noDiscount = false

Of course your real query will be more complicated because you will need to join in the appropriate tables, but this should give you the general approach.

Answer 5

There is an if statement in mysql that you can use. So, for example:

Select item_id, price*if(discount.type, discount.value, 1) 
from item join discount on item.item_id = discount.item_id

You might need to mess around with that query before it works in all cases, but it should work.

Answer 6

Lets say that you have an item_id on the Transactions Table, and that Percentage is represented by 'P', you could try something like

SELECT  *,
        CASE 
            WHEN td.T_id IS NULL
                THEN it.price
            WHEN td.T_id IS NOT NULL AND d.Type = 'P'
                THEN id.price - (id.price * d.value)
            ELSE id.price - d.value
        END PriceDiscounted

FROM    Transaction t LEFT JOIN
        Purchase p ON t.T_id = p.T_id LEFT JOIN
        Item it ON p.item_id = it.item_id LEFT JOIN
        Transaction_Discount td ON t.T_id = td.T_id LEFT JOIN
        Discount d ON td.D_id = d.D_id LEFT JOIN
        Item id ON d.item_id = id.item_id

Answer 7

Try this approach:

select i1.item_id, 
    i1.Price, 
    case when dt.item_id is null then i1.Price else i1.Price - dt.TotalDiscount end as DiscountedPrice
from Item i1
left outer join (
    select i.item_id, sum(
        case when d.type = 'percentage' then i.price * d.value / 100 else d.value end
    ) as TotalDiscount
    from Item i
    inner join Discount d on i.item_id = d.item_id
    group by i.item_id
) dt on i1.item_id = dt.item_id