Re: Complex join problem with lookup table - need Help

On 2 Apr 2002, asd_at_sds.com wrote:
>
> Using Oracle 8i 8.1.6
>
> We have a dimension table emergency_role
>
> Emergency_sq number
> Emergency_role_name varchar
>
> With data
> 1 patroller
> 2 wire watcher
> 27 engineer
>
>
>
> we have a fact table emergency_responder
>
> employee id number
> address varchar
> emergency_position_seq_number number
> alternate_position_seq_number number
>
> with data
>
> 324
> 16 smith road
> 2
> 1
>
> 534
> 1 north lane
> 27
> 2
>
>
> We need to display the data to the user with the name of the emergency
> role
>
> So instead of
>
> 324
> 2
> 1
>
> we have
>
> 324
> Wire Watcher
> Patroller
>
> The problem is in joining the tables together is that we have 2
> columns emergency_position_seq_number and
> alternate_position_seq_number that both have the same lookup table.
>
> Doing
>
> select * from emergency_role a, emergency_role b where
> a.Emergency_sq = b. emergency_position_seq_number

Where is the fact table?

> works for emergency_position_seq_number but not
> alternate_position_seq_number
>
> when I do
>
> select * from emergency_role a, emergency_role b where
> a.Emergency_sq = b.emergency_position_seq_number
> and
> a.Emergency_sq = b.alternate_position_seq_number

Where is the fact table?

> it only gives me rows where both emergency_position_seq_number and
> alternate_position_seq_number are the same.
>
>
>
> when I do
>
> select * from emergency_role a, emergency_role b where
> a.Emergency_sq = b.emergency_position_seq_number
> or
> a.Emergency_sq = b.alternate_position_seq_number

Where is the fact table?

> it gives me 10x the number of rows and all are wrong.
>
>
> What is the proper way to do this join?

I'm assuming you didn't cut and paste?

select lkp1.descriptive_fld1, lkp2.descriptive_fld1 from lkp1, lkp2, fact
where fact.id1 = lkp1.id1
and fact.id2 = lkp2.id1

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.