Post reply

List all the departments that don’t have any employees

  • October 29, 2008 at 3:45 pm

    #195265

    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.

  • October 29, 2008 at 9:04 pm

    #891583

    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 🙂

  • October 29, 2008 at 9:32 pm

    #891589

    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?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 29, 2008 at 9:38 pm

    #891593

    This should do it:

    Select DepartmentId, Departmentname

    From Department

    Left Outer Join Employee ON Employee.DepartmentID = Department.DepartmentID

    Where Employee.DepartmentID IS NULL

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 30, 2008 at 4:03 am

    #891669

    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

  • October 30, 2008 at 7:14 am

    #891741

    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 😛

  • October 31, 2008 at 6:05 am

    #892255

    I would group by department having count(*) = 0.

  • October 31, 2008 at 12:22 pm

    #892506

    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.

  • October 31, 2008 at 1:22 pm

    #892552

    Oh yeah, what on Earth was I thinking! The left outer join was definitely the way to go.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply