CODEWITHSUNDEEP
mysqlsql
Karem
Karem

Reputation: 18103

If row exist in a table then use another tables value in SQL

What i would like to archieve:

Getting the correct sum of the total amount of the orders that has been cancelled of user id 2002.

Some pre information:

I am having deals which that has its price in deals.price and its id in deals.ID

I then have orders with a foreign key to deals.ID

Running this SQL:

select SUM(deals.price), orders.* from orders
JOIN deals ON deals.ID = orders.deal_id
where orders.user_id = 2002
and orders.cancelled = 1

Works just fine.

Here is where i get stuck:

As an addition to deals, each deals has products with their own prices.

Table is called deal_products, deal_products.price hold the price and deal_products.product_id has the ID of it.

A order is attached to a deal product in another table called order_products, where order_products.product_id = deal_products.product_id

To sum up: I would like to do is including a if inside the above SQL.

If a order has a row in order_products, get the order_products.product_id and find the price in deal_products (price) and use this instead of deals.price when SUM()'ing.

If there is no row it should use deals.price.

How can this be archieved? To first look in another table if there is a entry, and then further look in to a third table and get a value to use?

Upvotes: 0

Views: 63

Answers (1)

Multisync
Multisync

Reputation: 8797

You can use COALESCE + LEFT JOIN:

select SUM(coalesce(dp.price, d.price)), o.* 
from orders o JOIN deals d ON d.ID = o.deal_id
              LEFT JOIN order_products op on op.order_id = o.id
              LEFT JOIN deal_products dp on op.product_id = dp.product_id
where o.user_id = 2002 and o.cancelled = 1
group by ...;

COALESCE function returns first not null operand

LEFT [OUTER] JOIN = [INNER] JOIN + all rows of the structure on the left side of the LEFT JOIN keyword, which don't match the ON clause in the right structure.

Upvotes: 3

Related Questions