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.

If you don't like the look of nested subqueries, you can take GarethD's great solution and convert it into a Common Table Expression (CTE)

WITH Transactions AS (
    SELECT ProductID, 'Bill' [Type], Amount FROM Bills
      UNION ALL
    SELECT ProductID, 'Ret'  [Type], Amount FROM Returns
)
SELECT  p.*,  [Type], Amount
FROM    Products p
JOIN    Transactions t
            ON t.ProductID = p.ProductID

Here's a demo in SqlFiddle