Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List all the departments that don’t have any employees Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 3:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2008 12:16 PM
Points: 2, Visits: 12
Hi,

I have Department Table having DepartmentId,Departmentname columns and Employee Table having EmployeeId,EmployeeName,DepartmentId.

I have to Display List all the departments that don’t have any employees(Without using sub query).

Please do reply as soon as possible.

Thanks,
Chow.
Post #593979
Posted Wednesday, October 29, 2008 9:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:03 PM
Points: 478, Visits: 1,416
Please post your DDL and some sample data in the format outlined in the following article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
That will help us to solve your problem for you :)
Post #594064
Posted Wednesday, October 29, 2008 9:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
naidussdba (10/29/2008)
I have to Display List all the departments that don’t have any employees(Without using sub query).


So why this unusual restriction?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #594070
Posted Wednesday, October 29, 2008 9:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
This should do it:
Select DepartmentId, Departmentname
From Department
Left Outer Join Employee ON Employee.DepartmentID = Department.DepartmentID
Where Employee.DepartmentID IS NULL



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #594074
Posted Thursday, October 30, 2008 4:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
rbarryyoung (10/29/2008)
naidussdba (10/29/2008)
I have to Display List all the departments that don’t have any employees(Without using sub query).


So why this unusual restriction?

Becuase it is an interview question ;)




Madhivanan

Failing to plan is Planning to fail
Post #594171
Posted Thursday, October 30, 2008 7:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:03 PM
Points: 478, Visits: 1,416
Madhivanan (10/30/2008)
rbarryyoung (10/29/2008)
naidussdba (10/29/2008)
I have to Display List all the departments that don’t have any employees(Without using sub query).


So why this unusual restriction?

Becuase it is an interview question ;)


Or a homework question :P
Post #594265
Posted Friday, October 31, 2008 6:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:11 AM
Points: 1,587, Visits: 1,920
I would group by department having count(*) = 0.
Post #594891
Posted Friday, October 31, 2008 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 7:36 AM
Points: 127, Visits: 517
Ian Massi (10/31/2008)
I would group by department having count(*) = 0.


won't the count be equal to or greater than 1 always? if the count is 0, then it doesn't exist

I guess if you did a left join a did a count on the employeeid which a count(null) = 0? since null's are excluded from aggrigate functions?

probably still faster to do there left join where emp.deptid is null since it won't require grouping everything together.
Post #595193
Posted Friday, October 31, 2008 1:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:11 AM
Points: 1,587, Visits: 1,920
Oh yeah, what on Earth was I thinking! The left outer join was definitely the way to go.
Post #595245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse