Join against two tables to get single combined result set

Question

tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products).

I have a Zen Cart ecommerce system on MariaDB and have a problem with the way order information is extracted into downstream order management systems. I have also looked into scripting in the order management system to modify the data, but it seems their API doesn't allow modification of orders at that stage (argh!) so I'm trying it via the SQL used as the orders are read from MariaDB into the order management system.

The scenario is where a coupon has been applied to an order, resulting in a discount, and I want that coupon's existence to appear in a resultset alongside the products in the order. So if you purchase Nuts for $100 and apply a $10 discount coupon, I want two rows in the resultset, one for Nuts at $80 and one for the coupon at $10, so the total equals $90 (the $100 cost less the $10 discount). The maths isn't the problem, the problem is that the existence of the products, and the existence of the coupon, are in two very different tables.

Zen Cart has three main tables:

• orders - one record per Order
• orders_totals - 4 or 5 records per Order, with things like Total, Sub-Total, VAT Content, Discount Coupon (this is the crux of my problem)
• orders_products - one record per product in each order.

I want to join orders against both orders_totals and orders_products to get one row per product, and an extra row if a row exists in orders_totals for that order that is a coupon.

Is there an elegant way to solve this? I'm not sure if a cunning JOIN is enough, or if a View or PARTITION may be required.

** Edit ** I made a new fiddle which takes the coupon amount, divides it by the number of products, and subtracts that portion from each product amount, combined with the UNION suggestion. See http://sqlfiddle.com/#!9/27ad50/3 .. but this seems quite ugly with all the subselects going on in the joins in the first half of the UNION, I hesitate to say this is a good solution!

Answer 1

In this case, and due Coupon is an specific entry of another table I'd use a UNION.

As a suggestion, I'd try to convert 'Coupon' entry of orders_total, into another product added as another row to the orders table.

SELECT orders_id, products_name as name, cost
FROM orders o 
JOIN  orders_products op on o.orders_id = op.fk_orders_id
WHERE o.orders_id = 1
UNION
SELECT fk_orders_id, name, total as cost
FROM   orders_totals
WHERE fk_orders_id = 1
AND   name = 'coupon'

Results:

| orders_id |   name | cost |
|-----------|--------|------|
|         1 |   Nuts |  100 |
|         1 | coupon |  -10 |

sqlfiddle here