Appending a Logical Field to View

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 2008 Forums

Transact-SQL (2008)

Appending a Logical Field to View

Author

Topic

Rainmann
Starting Member

5 Posts

Posted - 2011-08-04 : 12:58:58

I have a parent table of Departments and a child table of Employees. I'd like to create a view over Departments that has a logical field called something along the lines of 'HasEmployees' that would be set to true if any number of employees were in that department and to false if there were no employees.

I'm sure it's simple but I have no idea how to go about it.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-04 : 13:05:15

perhaps something like this:


select d.departmentid
       ,d.departmentName
       ,case when count(e.employeeid) > 0 then 1 else 0 end as hasEmployees
from   departments d
left outer join employees e on e.deptid = d.deptid
group by d.departmentid
       ,d.departmentName

EDIT:
This may be better as you can add as many department columns without needing a GROUP BY:


select d.departmentid
       ,d.departmentName
       ,case when count(e.employeeid) > 0 then 1 else 0 end as hasEmployees
from   department d
outer apply (select count(*) 
              from employees 
              where deptid = d.deptid) e

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 13:09:57

[code]
select d.departmentid
,d.departmentName
,case when e.empcnt > 0 then 1 else 0 end as hasEmployees
from departments d
outer apply (select count(*) as empcnt
from employees
where deptid = d.deptid) e
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-04 : 13:11:35

Ha! gotcha

Be One with the Optimizer
TG

Rainmann
Starting Member

5 Posts

Posted - 2011-08-04 : 16:53:04

Absolutely perfect!

Thank you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources