CODEWITHSUNDEEP
sqlsql-server
user6647541
user6647541

Reputation:

Reducing multiple rows into single rows

I'm trying to get all employees in my database that have a certain skill, such as plumbing. When I do the following query I am getting the data I want but it is showing that employee more than once.

SELECT
      TE.intEmployeeID
     ,TE.strLastName + ', ' + TE.strFirstName AS strEmployeeName
     ,CONVERT (VARCHAR, TE.dteHireDate, 101) AS dteHireDate
     ,TES.intSkillID
     ,TS.strSkills

 FROM
     TEmployees         AS TE
    ,TEmployeeSkills    AS TES
    ,TSkills            AS TS
    ,TJobEmployees      AS TJE
WHERE

        TE.intEmployeeID    =   TJE.intEmployeeID
    AND TS.intSkillID       =   TES.intSkillID
    AND TES.intEmployeeID   =   TE.intEmployeeID
    AND TES.intEmployeeID   =   TJE.intEmployeeID
    AND TES.intSkillID = 6

/*
What I am getting:

intEmployeeID   strEmployeeName   dteHireDate   intSkillID   strSkills
    2           Quagmire, Glen      06/10/2012      6         Plumbing
    4           Cage, Luke          01/10/2012      6         Plumbing
    4           Cage, Luke          01/10/2012      6         Plumbing
    2           Quagmire, Glen      06/10/2012      6         Plumbing
    2           Quagmire, Glen      06/10/2012      6         Plumbing
    4           Cage, Luke          01/10/2012      6         Plumbing

    What I need:

intEmployeeID   strEmployeeName   dteHireDate   intSkillID   strSkills
    2           Quagmire, Glen      06/10/2012      6         Plumbing
    4           Cage, Luke          01/10/2012      6         Plumbing
*/

Upvotes: 0

Views: 484

Answers (1)

SqlZim
SqlZim

Reputation: 38023

try select distinct instead of just select.

Also, I would recommend updating your join syntax.

select distinct 
      te.intEmployeeid
     ,te.strLastName + ', ' + te.strFirstName as strEmployeeName
     ,convert (varchar, te.dteHireDate, 101) as dteHireDate
     ,tes.intSkillid
     ,ts.strSkills
from temployees as te 
  inner join temployeeSkills as tes on tes.intEmployeeid = te.intEmployeeid 
    and tes.intSkillid = 6
  inner join tskills as ts          on ts.intSkillid     = tes.intSkillid
  inner join tjobEmployees as tje   on tje.intEmployeeid = te.intEmployeeid

Upvotes: 1

Related Questions