My Problem is the following: Say you have three different tables (Products, Bills and Returns)
| ProductId | Name |
=====================
| 1 | Car |
| BillId | ProductId | Amount |
=================================
| 1 | 1 | 100$ |
| 2 | 1 | 200$ |
| ReturnId | ProductId | Amount |
===================================
| 1 | 1 | 50$ |
How would a SINGLE Query look like to get the following output:
| Product-ID | Name | Type | Amount |
=====================================
| 1 | Car | Bill | 100$ |
| 1 | Car | Bill | 200$ |
| 1 | Car | Ret | 50$ |
I was trying with all sorts of Joins, and somehow I can't get my head around this. What am I doing wrong? The closest solution I have found till now was something like this:
SELECT p.*,
(CASE
WHEN b.Amount IS NOT NULL THEN 'Bill'
ELSE 'Ret'
END) AS Type,
COALESCE(b.Amount, r.Amount) AS Amount
FROM Products p
LEFT JOIN Bills b ON b.ProductId = p.ProductId
LEFT JOIN Returns r ON r.ProductId = p.ProductId
One thing is very important to me: The real scenario-query is MUCH bigger, and I don't want to copy/paste the whole logic of there query as if it would be the case when using a Union.
The below would work as needed,
SELECT Products.*,
[Type],
Amount
FROM Products
INNER JOIN
( SELECT ProductID, 'Bill' [Type], Amount
FROM Bills
UNION ALL
SELECT ProductID, 'Ret' [Type], Amount
FROM Returns
) transactions
ON transactions.ProductID = Products.ProductID