Need to select the one value if it exists otherwise another value

[szeiss]

I'm not sure how to go about this. In my tbl_Project_Employee below I have 4 dummy records. I need a query that selects where the employee_type_id = 48 if it exists; otherwise I select where the employee_type_id = 22. I've shown below the table what my results should be.

[pre]
tbl_Project_Employee
ProjectID		Employee_Type_ID	Employee_Type_Desc		Email	
001			        48	        Public_Contact			john.doe@anywhere.com
001				22	        Project_Manager			jane.doe@anywhere.com
002				48	        Public_Contact			susan.jones@anywhere.com
003				22	        Project_Manager			david.howard@anywhere.com

Results S/B:
ProjectID		Employee_Type_ID	Employee_Type_Desc		Email
001				48	        Public_Contact			john.doe@anywhere.com
002			        48	        Public_Contact			susan.jones@anywhere.com
003				22	        Project_Manager			david.howard@anywhere.com
[/pre]

Thanks,
Sherry

 

[johnherman]

I am not sure if Employee_Type_ID is numeric or character - I have assumed Char:

CASE
WHEN select * from tbl_Project_Employee where Employee_Type_ID = '48' IS NULL
THEN select * from tbl_Project_Employee where Employee_Type_ID = '22'
ELSE select * from tbl_Project_Employee where Employee_Type_ID = '48'
END

There is a better way to do this, and someone will post it. But this works and you should learn the CASE syntax.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[szeiss]

Thank you for the reply. A co-worker was able to free up some time and help me out with this. I am familiar with the CASE syntax just wasn't successful in making it work. But, this is what we came up with, we also added a little more information to the query.

[pre]
select
s.ProjectID,
CASE
WHEN pc.EMPLOYEE_ID IS NOT NULL THEN
DECODE(uc.email_addr,NULL,lpc.firstname||'.'||lpc.lastname||'@anywhere.com',uc.email_addr)
ELSE
DECODE(um.email_addr,NULL,lm.firstname||'.'||lm.lastname||'@anywhere.com',um.email_addr)
END as CONTACT,
pc.EMPLOYEE_TYPE_ID,
pc.EMPLOYEE_ID,
(SELECT EMPLYSTAT FROM LIST_EMPLOYEE_INFO i where i.EMPLOYEE_ID = pc.EMPLOYEE_ID) as CONTACT_EMP_STATUS,
m.EMPLOYEE_TYPE_ID,
m.EMPLOYEE_ID,
(SELECT EMPLYSTAT FROM LIST_EMPLOYEE_INFO i2 where i2.EMPLOYEE_ID = m.EMPLOYEE_ID) as MANAGER_EMP_STATUS
from
subprojects s,
subproject_employee pc,
subproject_employee m,
cip_users uc,
cip_users um,
list_employees lpc,
list_employees lm
where
s.ProjectID = pc.ProjectID (+)
and pc.employee_type_id(+) = 48 -- public contact
and pc.employee_id = uc.employee_id(+)
and pc.employee_id = lpc.employee_id(+)

and s.ProjectID = m.ProjectID(+)
and m.employee_type_id(+) = 22 -- subproject manager
and m.employee_id = um.employee_id(+)
and m.employee_id = lm.employee_id(+)
[/pre]

 

[carp]

One alternative to the CASE construct would be

SELECT * FROM tbl_project_employee 
WHERE (project_id, employee_type_id) IN (SELECT project_id, max(employee_type_id) 
                                           FROM tbl_project_employee 
                                          WHERE employee_type_id IN (22,48) 
                                          GROUP BY project_id);

This may or may not be a more efficient approach depending on table size, value mixture, etc, particularly if you use this as a dynamic view.
Of course, this is answering your original question. Looking at the solution you came up with, I think it would definitely be more efficient since you would not have to be doing outer joins on twin sets of tables. The resultant query would take a general form of

SELECT p.project_id,
       p.email AS contact,
       p.employee_type_id,
       p.employee_type_desc,
       e.employee_id,
       i.emplystat AS contact_status
  FROM (SELECT * FROM tbl_project_employee 
         WHERE (project_id, employee_type_id) IN (SELECT project_id, max(employee_type_id) 
                                                    FROM tbl_project_employee 
                                                   WHERE employee_type_id IN (22,48) 
                                                   GROUP BY project_id)) p
       INNER JOIN list_employees e
          ON e.email = p.email 
       INNER JOIN list_employee_info i
          ON i.employee_id = e.employee_id;

This hasn't been tested beyond the dynamic view, but I think it would work. Of course, if your current solution is working for you, this is all incidental.

 

[szeiss]

Thank you, I'll take a look at it and keep it for future reference.