Querying multiple fact tables

[biff2002]

I am looking to build a query involving multiple fact tables which share a common dimension key - can anyone shed some light on the accurate way to do this?

Eg Customer dimension table has customer_key
Orders fact table has a list of all orders, including the customer_key involved
Returns fact table has a list of all returns, including the customer_key involved.

A query such as

select *
from customer c,
     orders o,
     returns r
where c.customer_key = o.customer_key (+)
and   c.customer_key = r.customer_key (+)

appears to return me one row for each matching row in orders and returns, as opposed to all orders and all returns information for each customer, which is my aim.

Any pointers appreciated

 

[bojisheng]

What DB are you using (looks like Oracle?)

SQL97 statement that should do this would be...
select *
from customer c,
left outer join orders o
on (c.customer_key = o.customer_key )
left outer join returns r
on (c.customer_key = r.customer_key )
where (r.customer_key is not null or o.customer_key is not null)
;

If most of your customers do not have orders and/or returns,
I would advise a multipass solution for performance (depending on your DB and system) to only have the keys that have both
like
insert into mykeys
select distinct customer_key from orders
union select customer_key from returns
;

then use mykeys instead of customer and omit the where clause.

*NOTE: That a union may not perform and cause you to insert both sets of keys into 1 table (having duplicates) then a select distinct (or group by) into another table to be used for distinct keys for the left outer join (instead of customer)

Hope this helps...sorry if I stated the obvious to you.