Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Complex join problem with lookup table - need Help

Re: Complex join problem with lookup table - need Help

From: Vince <vinnyop_at_yahoo.com>
Date: 3 Apr 2002 07:58:11 -0800
Message-ID: <56e2f55a.0204030758.3d0f0bff@posting.google.com>


Try joining like this:

select a.employee_id, 
       b.emergency_role_name as emergency_posisition,
       c.emergency_role_name as alternate_position
from   emergecy_respond a, 
       emergency_role b, 
       emergency_role c

where a.emer_pos_seq_no = b.emer_sq
and a.alter_pos_seq_no = c.emer_sq(+)

asd_at_sds.com (fd) wrote in message news:<3caa640e$0$41252$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_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 Wed Apr 03 2002 - 09:58:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US