Complex join problem with lookup table - need Help

From: fd <asd_at_sds.com>
Date: 2 Apr 2002 20:11:04 -0600
Message-ID: <3caa640e$0$41252$45beb828@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_role 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_role 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_role 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? Received on Tue Apr 02 2002 - 20:11:04 CST