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

salut,

"fd" <asd_at_sds.com> schrieb im Newsbeitrag 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?

I think you have to do a union

select a.*, b.* from a, b where a.E..._seq = b. union
select a.*, b.* from a, b where a.E..._seq = b.Alternate... group by a.Emergen...seq

Matthias Received on Wed Apr 03 2002 - 07:09:20 CST