Re: Complex Join problem with 1 lookup table any ideas?

asd_at_sds.com (fd) wrote in message news:<3caae9fc$0$2125$45beb828_at_newscene.com>...
> 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_responder b where
> a.Emergency_sq = b. emergency_position_seq_number
>
> works for emergency_position_seq_number but not alternate_position_seq_number
>
> when I do
>
> select * from emergency_role a, emergency_responder b where
> a.Emergency_sq = b.emergency_position_seq_number
> and
> a.Emergency_sq = b.alternate_position_seq_number
>
> 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_responder b where
> a.Emergency_sq = b.emergency_position_seq_number
> or
> a.Emergency_sq = b.alternate_position_seq_number
>
> it gives me 10x the number of rows and all are wrong.
>
>
> What is the proper way to do this join with just one lookup table
> emergency_role?

You will need to use the emergency_role table twice:

select b.employee_id, b.address, a.emergency_role_name, c.emergency_role_name from emergency_role a, emergency_responder b, emergency_role c where
b.emergency_position_seq_number = a.Emergency_sq(+) and b.alternate_position_seq_number = c.Emergency_sq(+) Received on Wed Apr 03 2002 - 11:11:57 CST