Reducing multiple rows into single rows

Question

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
*/

some of the tables may have a one-many relationship. use `distinct` to get unique rows. — Vamsi Prabhala, Dec 13 '16 at 19:19
Possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Tab Alleman, Dec 13 '16 at 19:19
Couple things: change your first 4 `WHERE` clauses into `INNER JOIN` clauses; also, I don't see where do you need the `TJobEmployees` table. Maybe that's the table that's causing duplicates. — Josh Part, Dec 13 '16 at 19:28

SqlZim · Accepted Answer · 2016-12-13T19:27:26.617

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

Reducing multiple rows into single rows

1 Answers1

Linked