Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server Newbies » List all the departments that don’t have any...
Add to briefcase

List all the departments that don’t have any employees Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
naidussdba
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
Goldie Lesser
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: Tuesday, April 16, 2013 6:34 AM
Points: 476, Visits: 1,361
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
RBarryYoung
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: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
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
RBarryYoung
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: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
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
Madhivanan-208264
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: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461
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
Goldie Lesser
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: Tuesday, April 16, 2013 6:34 AM
Points: 476, Visits: 1,361
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
Ian Massi
Posted Friday, October 31, 2008 6:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 1,303, Visits: 1,661
I would group by department having count(*) = 0.
Post #594891
bcronce
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 03, 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
Ian Massi
Posted Friday, October 31, 2008 1:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 1,303, Visits: 1,661
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.