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

From: Ed prochak <ed.prochak_at_alltel.com>
Date: 3 Apr 2002 07:52:11 -0800
Message-ID: <e51b160.0204030752.4eaa5765@posting.google.com>

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.
>

[snip]
> What is the proper way to do this join with just one lookup table
> emergency_role?

I'm quoting the id column name since it has spaces in it. I also renamed the table aliases to hopefully show a little better what you are doing.

select "employee id",

       primary_role.Emergency_role_name,
       secondary_role.Emergency_role_name
 from emergency_role      primary_role,
      emergency_responder responder,
      emergency_role      secondary_role

where
  primary_role.Emergency_sq

• responder.emergency_position_seq_number and secondary_role.Emergency_sq
• responder.alternate_position_seq_number ;

That wasn't so hard was it?

Don't be confused that the table is there twice. Think about the ROWS. You need two different rows from the emergency_role table, so you need to join the table twice.

 To really understand what's going on, go read about relational database concepts. Learn what JOINING TABLES really means.

HTH,
  Ed

Edward J. Prochak --- Magic Interface, Ltd. Ofc: 440-498-3700 Cell: 440-666-9013
on the web at --- http://www.magicinterface.com email: ed.prochak_at_magicinterface.com Received on Wed Apr 03 2002 - 09:52:11 CST