Please help with self join

[sql99]

Hello everyone,

I have the following select statement:

select comp_dir, comp_poc, comp_role, empl.add,
empl.phone, empl.fax
from company comp,
employee empl
where comp.comp_id = empl.company.id
and comp.active_flg = 'Y';

This select gives me the address,phone, and fax for the director, pm and poc like below

comp_dir comp_poc comp_role empl.add empl.phone empl.fax
Joe Han Sam Jones Director Brady Ct 888-8888 899-9999
Joe Han Sam Jones PM Collin St 889-9876 778-9887
Joe Han Sam Jones POC Carlin Rd 876-8999 676-8777

Most of the companies in the database have 3 roles assigned to it, a director, a pm, and a poc. i would only like the empl_add, empl_phone, and empl_fax to show up only for the POC. In other words, I would like the record to show like below:

comp_dir comp_poc comp_role empl.add empl.phone empl.fax
Joe Han Sam Jones POC Carlin Rd 876-8999 676-8777

I don't want the empl.add, empl.phone, and empl.fax to appear for the other roles. That will eliminate multiple rows from showing up.

Hope I didn't confuse anyone.

Thanks in advance for your help...

sql99

 

[SantaMufasa]

99,

Please correct me if I am misunderstanding, but won't you get what you want if you say:

select comp_dir, comp_poc, comp_role, empl.add, 
       empl.phone, empl.fax
  from company comp,
       employee empl
 where comp.comp_id = empl.company.id
   and comp.active_flg = 'Y'
   [b]and comp_poc = 'POC'[/b];

This gives you just the POC row.

Let us know if this is what you wanted, otherwise what you want that is different.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.