Case in with subselect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case in with subselect

Author

Topic

datahappy
Starting Member

1 Post

Posted - 2010-07-30 : 19:11:34

I need help with my case statement for a new view.
Here is the select I have with my attempt at the case.
The EJG table has employee id and jobgroup id as does the EJGS table.
I need to add a column to the view to represent when the records match the EJG with a 1 and if they don't match a 0.
Any help is much appreciated! :)

select ejg.record_id,
ejg.employee_id,
e.contact_name,
e.is_active,
ejg.jobgroup_id,
lv1.lookup_value as jobgroup,
(select case (when exists ejgs.employee_id = ejg.record_id
and ejgs.jobgroup_id = ejg.jobgroup_id then '1'
else when not exists ejgs.employee_id = ejg.record_id
and ejgs.jobgroup_id = ejg.jobgroup '0')) as is_supervisor,
ejg.created,
ejg.create_by,
ejg.last_update,
ejg.last_update_by
from employeejobgroup ejg inner join employee e
on ejg.employee_id = e.record_id
left outer join employeejobgroupsupervisor ejgs
on ejgs.employee_id = ejg.employee_id
and ejgs.jobgroup_id = ejg.jobgroup_id
inner join lookupvalue lv1
on ejg.jobgroup_id = lv1.record_id

Kokkula
Starting Member

41 Posts

Posted - 2010-08-05 : 05:18:48

Hello,

Try this...

SELECT ejg.record_id,
ejg.employee_id,
e.contact_name,
e.is_active,
ejg.jobgroup_id,
lv1.lookup_value as jobgroup,
CASE WHEN ejgs.employee_id IS NOT NULL THEN 1 ELSE 0 END AS is_supervisor,
ejg.created,
ejg.create_by,
ejg.last_update,
ejg.last_update_by
FROM employeejobgroup ejg
JOIN employee e
ON ejg.employee_id = e.record_id
LEFT OUTER JOIN employeejobgroupsupervisor ejgs
ON ejgs.employee_id = ejg.employee_id
and ejgs.jobgroup_id = ejg.jobgroup_id
JOIN lookupvalue lv1
ON ejg.jobgroup_id = lv1.record_id

Hope its helpful....

Pavan
Infosys Technologies Limited

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources