If you don't like the look of nested subqueries, you can take [GarethD's great solution][GarethD] 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][fiddle]

[GarethD]: https://stackoverflow.com/a/9032048/1366033
[fiddle]: http://sqlfiddle.com/#!18/336a8/1

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.