Sql Statement

• ui ranejeb

  SSC Enthusiast

  Points: 148

  More actions

  February 20, 2005 at 5:16 pm

  #92450

  I have 3 tables:

  Employee (EmployeeId, FirstName, LastName)

  Project(ProjectId, Name)

  EmployeeProject(EmployeeId, ProjectId)

  I need to find out the employees who have worked for all the projects in the PROJECT table (just ONE select statement).

  Thank you

• Helen-84411

  Hall of Fame

  Points: 3320

  More actions

  February 20, 2005 at 9:45 pm

  #543250

  Try this

  select EmployeeId from EmployeeProject group by EmployeeId having count(*) >= (select count(distinct ProjectId) from Project)

   

  Is it what you want

  cheers

  
  Helen
  --------------------------------
  Are you a born again. He is Jehova Jirah unto me

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 22, 2005 at 6:58 am

  #543451

  Just another option.

  First Off I am assuming ProjectID is Unique in Project and ProjectId is Unique per EmployeeId in EmployeeProject (meaning an employee can only be associated once).

  However if the design changes later to add say a role for employee per project this will still allow full flexibility for that.

  SELECT

   E.*

  FROM

   dbo.Employee E

  INNER JOIN

   (

    SELECT

     EP.EmployeeID

    FROM

     dbo.EmployeeProject EP

    INNER JOIN

     dbo.Project P

    ON

     P.ProjectId = EP.ProjectId

    GROUP BY

     EP.EmployeeID

    HAVING

     COUNT(DISTINCT EP.ProjectId) = (SELECT COUNT(IX.ProjectId) ix_proj FROM dbo.Project IX)

  &nbsp OX

  ON

   OX.EmployeeID = E.EmployeeID

   

  The previous will not be flexible for tht scenario but as long as never occurrs will be fine but I would use = instead of >=.