self join?? 1

[sql99]

Hello everyone,

Sorry - I've recently submitted the same question but was away for a week and never had a chance to reply to the original thread. This is my script:

SELECT tv.pm_name, tv.tech_name, empl.f_name, empl.l_name,
empl.address, empl.phone, empl.fax
FROM task_view tv, employee empl, role rol
WHERE tv.emp_id = empl.ind_id
AND empl.ind_id = rol.role_id
AND empl.archive = 'N'
AND rol.role_code = 'DR';

This is what it's returning:

PM_NAME TECH_NAME F_NAME L_NAME ADDRESS PHONE FAX
Jack Do Amy Jones Amy Jones Burke Ct 888-8888 889-0098 Jack Do Amy Jones Jack Do Fair Ln 876-9888 766-8999 Jack Do Amy Jones Sam Jim Main St 875-8767 763-8787

Basically Jack Do is a PM, Amy Jones is a tech lead, and Sam Jim is a director the the contract above. I want to write a select so that it will only return the f_name, l_name, address, phone, and fax for the director if there is one for the contract. Istead of the multiple rows above, I would only like to display one row like below:

PM_NAME TECH_NAME F_NAME L_NAME ADDRESS PHONE FAX
Jack Do Amy Jones Sam Jim Main St 875-8767 763-8787

The contracts will always have a pm and a tech lead but not necessarily a director. If the contract doesn't have a director (DR), then the f_name, l_name, address, phone and fax will just be blank.

Please let me know if you don't understand what I'm trying to do.

I'm thinking there needs to be a self-join for the select.

Can someone please help?

Thanks in advance,
sql99

 

[SantaMufasa]

SQL99,

Could you please clarify some things for me?

1) It appears to me that Jack Do, Amy Jones, and Sam Jim all have a role_code='DR', else their rows would not appear.

2) Is there a contract ID or some other value that associates persons with the same contract?

I am also reposting your sample output (in a code block) to make it easier for other to read:

PM_NAME TECH_NAME F_NAME L_NAME ADDRESS  PHONE    FAX
------- --------- ------ ------ -------- -------- --------
Jack Do Amy Jones Amy    Jones  Burke Ct 888-8888 889-0098
Jack Do Amy Jones Jack   Do     Fair Ln  876-9888 766-8999
Jack Do Amy Jones Sam    Jim    Main St  875-8767 763-8787

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.

 

[sql99]

Thanks for your reply Dave.

Sorry, I mistyped the script. It should be like this:

SELECT tv.pm_name, tv.tech_name, empl.f_name, empl.l_name,
empl.address, empl.phone, empl.fax
FROM task_view tv, employee empl, role rol
WHERE tv.contract_id = rol.cpmtract_id
AND rol.ind_id = empl.ind_id
AND rol.archive = 'N';


The task_view is a view that already captures the names of the pm and tech lead. I'm just pulling their names straight from there. The role table is linked to the task_view table by contract id. It captures the role ids (example - pm, tech, dir) based on the contract. I would need to specify somehow to only pull the f_name, l_name, address, phone and fax from the employee table only if the contract has an existing rol.role_code = DR - meaning that a director is assigned to the contract. If it only has roles PM and TECH, then this information can just be blank.

I hope that's a little clearer...please let me know if you need more info.

Thanks,
sql99

 

[SantaMufasa]

The code you posted above cannot work as long as you reference a table named, "ROLE" (an Oracle reserved word). I'll refer to it as "ROLE_TAB" to avoid syntax errors.

Could you please post sample results of:

SELECT * from task_view;
SELECT * from employee;
SELECT * from role_tab;

...for just the rows that apply to your example? I believe that seeing sample data from the tables will answer my remaining questions so that I can propose some working code.

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.

 

[sql99]

Hi Dave,

I hope this is what you're looking for, if not, please let me know...

select * from task_view:
contract_id pm_name tech_name
145 Jack Do Amy Jones
145 Jack Do Amy Jones
145 Jack Do Amy Jones

select * from employee:
ind_id f_name l_name address phone fax
256 Jack Do Fair Ln 876-9888 766-8999
789 Amy Jones Burk Ct 888-8888 889-0098
977 Sam Jim Main St 875-8767 763-8787

select * from role_tab:
contract_id ind_id archive role_cd
145 256 N PM
145 789 N TECH
145 977 N DR

All contracts must have a PM and a TECH assigned to it but not necessary a DR like example below:

contract_id ind_id archive role_cd
447 197 N PM
447 852 N TECH

Please let me know if you need anything else.

Thanks,
sql99

 

[SantaMufasa]

SQL99,

Your post of table contents was extremely useful. It gave me a clear picture of your data behaviour and relationships. It also clearly indicates that the "TASK_VIEW" data is extraneous since all the data you need reside in the other two tables, "ROLE_TAB" and "EMPLOYEE".

To adequately test the SQL code's proof of concept, I added in rows to both tables ("ROLE_TAB" and "EMPLOYEE") that reflect the condition of "no DR". Following, then, represents the data sets and SQL query that reflect a resolution to your need:

Section 1 -- Data sets:

select * from employee;

IND_ID F_NAME     L_NAME     ADDRESS         PHONE      FAX
------ ---------- ---------- --------------- ---------- --------
   256 Jack       Do         Fair Ln         876-9888   766-8999
   789 Amy        Jones      Burk Ct         888-8888   889-0098
   977 Sam        Jim        Main St         875-8767   763-8787
   197 SQL        99         Virginia St     999-8888   999-7777
   852 Dave       Hunt       Utah Av         777-3333   777-4444

select * from role_tab;

Contract
 Num         IND_ID A ROLE
-------- ---------- - ----
 145            256 N PM
 145            789 N TECH
 145            977 N DR
 447            197 N PM
 447            852 N TECH

Section 2 -- SQL Query to produce your requested results:

col aa heading "Cont|Num" format 999
col a heading "PM|Name"    format a10
col b heading "Tech|Name"  format a10
col c heading "DR|Name"    format a10
col d heading "DR|Address" format a11
col e heading "DR|Phone"   format a10
col f heading "DR|Fax"     format a10
select cv.contract_id                  aa
      ,epm.f_name||' '||epm.l_name     a
      ,etech.f_name||' '||etech.l_name b
      ,edr.f_name||' '||edr.l_name     c
      ,edr.address                     d
      ,edr.phone                       e
      ,edr.fax                         f
from (select c.contract_id
            ,pm.ind_id pm_id
             ,tech.ind_id tech_id
             ,dr.ind_id dr_id
       from (Select distinct contract_id from role_tab) c
           ,role_tab pm
           ,role_tab tech
           ,role_tab dr
       where c.contract_id = pm.contract_id
         and c.contract_id = tech.contract_id
         and c.contract_id = dr.contract_id(+)
         and pm.role_cd = 'PM'
         and tech.role_cd = 'TECH'
         and nvl(dr.role_cd(+),'DR') = 'DR') cv
     ,employee epm
     ,employee etech
     ,employee edr
where cv.pm_id   = epm.ind_id
  and cv.tech_id = etech.ind_id
  and cv.dr_id   = edr.ind_id(+)
order by aa
/

Cont PM         Tech       DR         DR          DR         DR
 Num Name       Name       Name       Address     Phone      Fax
---- ---------- ---------- ---------- ----------- ---------- --------
 145 Jack Do    Amy Jones  Sam Jim    Main St     875-8767   763-8787
 447 SQL 99     Dave Hunt

Let us know if you have questions and if this satisfactorily resolves you need.

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.

 

[sql99]

Thank you so much Dave...It works...)

I'm so glad you can understand what I was trying to do...