Need to select the one value if it exists otherwise another value
Need to select the one value if it exists otherwise another value
(OP)
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.
Thanks,
Sherry
CODE --> sql
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
Thanks,
Sherry
Talk To Other Members
RE: Need to select the one value if it exists otherwise another value
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
RE: Need to select the one value if it exists otherwise another value
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(+)RE: Need to select the one value if it exists otherwise another value
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);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;RE: Need to select the one value if it exists otherwise another value