graemeo


Hi,

I'm having trouble with a left join.

I have a 'parent' table which I want to join to two other tables for use as a fact table in an OLAP cube.

Currently I have the following query:

SELECT A.*, B.col1, B.col2, C.col1, C.col2
FROM TABLE1 A
LEFT JOIN TABLE2 B
ON A.ID = B.ID
LEFT JOIN TABLE3 C
ON A.ID = C.ID

This query returns more rows than a simple 'SELECT * FROM TABLE1' .

The specific problem is that in TABLE1, I have a column 'SALES' that gets totalled up in the cube viewer. When I use ONLY table1 as the fact table, the totals are correct. However when I join table1 to the other tables and use the resultant view, the totals are much higher than they should be.



Re: LEFT JOIN question

Waldrop


Graemeo:

If either table2 or table3 have a many to one relationship to table1 this outcome should be expected. This is because now the SALES column will get repeated for each additional occurrence in either table2 or table 3 for this particular query.


Dave