I have the following tables:
Table users
id name base_discount
1 jack 10
2 michael 20
3 richard 30
Table item
id name category_id price
1 hammer 1 10
2 knife 2 15
3 spoon 2 12
4 plate 3 20
5 tree 4 400
Table category
id name
1 tools
2 kitchen
3 dishes
4 garden
Table discount_category
id user_id category_id discount
1 1 1 20
2 1 3 25
3 3 3 10
4 1 2 15
Table discount_item
id user_id item_id discount
1 2 1 50
2 1 2 50
Now what I want to achieve. I want to attach the discount per item that a user has to the correct item. If that is not available (NULL) I want to attach the discount per category that a user has. And if that is not available (NULL), I want to attach the base discount that a user has. With the discount I then calculate the new price of the item. However, when I try using COALESCE() within SUM() I get a syntax error. What am I doing wrong?
Below is my current query:
SELECT item.id, item.name, category.id,
category.name AS category_name, item.price, SUM((100 -
COALESCE(
(
SELECT discount_item.discount
FROM discount_item
INNER JOIN users ON discount_item.user_id = users.id
WHERE users.id = '1' AND discount_item.item_id = item.id
),
(
SELECT discount_category.discount
FROM discount_category
INNER JOIN users ON discount_category.user_id = users.id
WHERE users.id = '1' AND discount_category.category_id = item.category_id
),
(
SELECT users.base_discount
FROM users
WHERE users.id = '1'
)
)) / 100 * item.price) AS new_price,
FROM item
INNER JOIN category ON item.category_id = category.id;
Please also see the below link for an SQL Fiddle (couldn't do it on sqlfiddle.com as it wouldn't load). In the example I have appended a suffix to each table name. http://rextester.com/LCCKSD59098