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

szeiss · Jul 17, 2019

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.

SQL:

[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 · Jul 19, 2019

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 · Jul 22, 2019

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 · Aug 5, 2019

One alternative to the CASE construct would be

Code:

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

Code:

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 · Aug 5, 2019

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

szeiss

Programmer

johnherman

MIS

szeiss

Programmer

carp

MIS

szeiss

Programmer

Similar threads

Part and Inventory Search

Sponsor