Join multiple Tables by date i got some problems on my approach to join a couple of tables by a date range. This are my tables:

RETURNS:

RETURN_DATE     RETURN_AMOUNT
02.05.14        2
02.05.14        3
02.05.14        1
02.05.14        7
02.05.14        5
02.05.14        2
02.05.14        1


and

ORDERS:

ORDER_DATE      ORDER_AMOUNT
02.05.14        50


As a first step, i created a date range and then tried to join these two tables to it. This is my SELECT statement:

SELECT
  DAT,
  RETURN_AMOUNT,
  ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM 
      all_objects

      WHERE 
      ROWNUM &lt;= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) LEFT JOIN RETURNS ON RETURN_DATE = DAT
    LEFT JOIN ORDERS ON ORDER_DATE = DAT

    ORDER BY DAT;


This was the Result:

RESULT:

DAT         RETURN_AMOUNT   ORDER_AMOUNT
02.05.14    1               50
02.05.14    2               50
02.05.14    5               50
02.05.14    2               50
02.05.14    1               50
02.05.14    3               50
02.05.14    7               50


Thats a problem because when i GROUP BY DAT and then sum up the amounts, i got a wrong ORDER_AMOUNT for this day.

How can i achive, that ORDER_AMOUNT is only resulted once?

Thanks,
Stefan.
You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM &lt;= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) 
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY 
  r.dat ;




Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount) 
              FROM returns  r 
              WHERE r.dat = d.dat
            ), 0) 
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount) 
              FROM orders  o 
              WHERE o.dat = d.dat
            ), 0) 
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM &lt;= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY 
  d.dat ;