Get the final price with discount but only if column is not NULL. SQL

I use these sql tables with these columns:

customers:

id	name	phone	adress	etc..
1234	Test Name	Test Phone	Test Adress	etc data.

orders:

customerid	orderid	orderdate
1234	OR_1234	2022-1-1

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

id	orderid	productid
1	OR_1234	P1

products:

productid	productprice	currency	qty	name	weight
P1	10	USD	1	TEST	0.2 KG

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 is working perfectly. But what if I want to add to this query a discount from discount table:

discount:

id	name	value	status
1	Discount	1	valid

So I think I will need to create one more column in 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!

Answer

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