I use these sql tables with these columns:

customers:

orders:

orderitems: (in this table one customer can have multiple rows(items)

products:

So in this case if I want to get the FULL price from the order from customer I use this query:

SELECT sum( productprice ) as fullprice
FROM customers 
inner join orders on orders.customerid = customers.id 
inner join orderitems on orderitems.orderid = orders.orderid 
inner join products on products.productid = orderitems.productid 
WHERE customers.id = '1234' 

This query working perfectly. But what if I want to add to this query a discount with discount table:

discount:

So I think I will need to crate one more column to orders table with name for example: discount_code and if the discount_code column is not empty than subtract the discount value from productprice.

SELECT sum( productprice - discount.value ) as fullprice but how can I make this query? Thank you for help!

BTW I use MariaDB

Have a very nice day!

CodePudding user response：

If you want to subtract only when your new column is not null, you could simply use IF() func inside you SUM()

In very simple example, assuming you added discount_code

create table Orders
(
  id int NOT NULL,
  price int NOT NULL,
  discount_code int NULL  
);

create table Discounts 
(
  id int not null,
  value int not null
);


insert into Orders
values
(1, 10, null),
(2, 10, null),
(3, 5, 1),
(4, 25, 1);


insert into Discounts
values
(1, 3);

select sum(if(o.discount_code is not null, o.price - d.value, o.price))
from Orders as o
left join Discounts as d
on o.discount_code = d.id;

-- 10   10   2   22 = 44

You can also run the example here