TSQL Distinct in Select Statement with Joins

Question

I have a query that is providing the correct data to me but if there are multiple records in the tuitionSubmission table for the same empID, it shows duplicate results (as expected).

I am trying to use a distinct selector on this query on A.[empID]

Below is my Query:

SELECT A.[empID], A.[id], A.[empGradDate], A.[status], A.[reimbursementDate], A.[firstName], A.[lastName], A.[businessTitle] AS department, B.[SupEmpID], B.[ntid] AS empNTID, B.[GeoLocationDesc] AS location, C.[FirstName] + ' ' + C.[LastName] AS supervisor, C.[ntid] AS supNTID, C.[SupEmpID], D.[FirstName] + ' ' + D.[LastName] AS manager, D.[ntid] AS managerNTID FROM tuitionSubmissions AS A INNER JOIN empTable AS B ON A.[empID] = B.[EmpID] INNER JOIN empTable AS C ON C.[empID] = B.[SupEmpID] INNER JOIN empTable AS D ON D.[empID] = C.[SupEmpID] WHERE B.[EmpID]= COALESCE(@ntid, B.[EmpID]) OR B.[SupEmpID]= COALESCE(@supervisor, B.[SupEmpID]) OR C.[SupEmpID]= COALESCE(@manager, C.[SupEmpID]) OR A.[EmpID]= COALESCE(@empName, C.[EmpID]) OR B.[GeoLocationDesc]= COALESCE(@theLocation, B.[GeoLocationDesc]) OR B.[SiloDesc]= COALESCE(@department, B.[SiloDesc]) FOR XML PATH ('details'), TYPE, ELEMENTS, ROOT ('root');

The table tuitionSubmissions can contain multiple records for the same user (same empID) but I only want to show one of them

any one, its the same user its finding they just happened to have multiple submissions in the tuitionSubmissions table. All im doing is throwing a button next to it containing their ID number to then pull all their records. — SBB, May 22 '14 at 16:09
Can you add examples of the output you are seeing and the output you expect to see? — Ellesedil, May 22 '14 at 16:52

JBrooks · Answer 1 · 2014-05-22T16:52:28.667

0

You can get what you want by adding the last part below to your where clause

WHERE
   (B.[EmpID]= COALESCE(@ntid, B.[EmpID]) OR
            B.[SupEmpID]= COALESCE(@supervisor, B.[SupEmpID]) OR
            C.[SupEmpID]= COALESCE(@manager, C.[SupEmpID]) OR
            A.[EmpID]= COALESCE(@empName, C.[EmpID]) OR
            B.[GeoLocationDesc]= COALESCE(@theLocation, B.[GeoLocationDesc]) OR
            B.[SiloDesc]= COALESCE(@department, B.[SiloDesc]))
    AND A.[id] IN
      (SELECT MAX(AMax.id)
       FROM tuitionSubmissions AS AMax
       GROUP BY AMax.empID)

edited May 22 '14 at 16:52

answered May 22 '14 at 16:26

JBrooks

9,901
2
28
32

I tried this but I still get both records where the empID is the same – SBB May 22 '14 at 16:29
You need () around your OR statements. – JBrooks May 22 '14 at 16:38
Added them, still pulls both records from tuitionSubmissions where both records have the same empID – SBB May 22 '14 at 16:41
http://oi60.tinypic.com/255jn1k.jpg - Both of those records are showing up on my page with that query being ran. – SBB May 22 '14 at 16:44
Then you have duplicate empNo records in one of the other tables. – JBrooks May 22 '14 at 16:53

score 0 · Accepted Answer · edited May 23 '17 at 10:26

The comment:

"I only want to show one of them" which one?– usr

is extremely important, because that's the crux of the issue. If you have duplicates, you need some rule to determine which of two duplicates is correct. If the duplicate rows are 100% identical, then you're missing a Primary Key. (And now you see why they're so important.)

Even if all you do is add a RowId IDENTITY column so that you know which of the duplicates was more recently added, this will go a long way to making your life easier. I'd also suggest you go about removing your duplicates, they're not contributing any information, and simply wasting space. Here's a question asking how to remove duplicates which should help.

However, if you want to hang on to the duplicates, you can complicate your query by removing them "on the fly".

;WITH UniqueSubs AS (
        SELECT  DISTINCT
                [id], [empGradDate], [status], [reimbursementDate],
                [firstName], [lastName], [businessTitle], [EmpID]
        FROM    tuitionSubmissions
      )
SELECT  A.[empID],
/*      ... And the rest of your query as is.
        ... Just substitute tuitionSubmissions with UniqueSubs.
*/

However, since you have an id column, perhaps you already have that IDENTITY column I mentioned earlier. This can be used as a "uniqueifier" to get the most recent version of tuitionSubmissions per EmpID as follows:

;WITH RecentSubs AS (
        SELECT  EmpID, MAX(id) AS MaxID
        FROM    tuitionSubmissions
      ),
      UniqueSubs AS (
        SELECT  ts.*
        FROM    RecentSubs rs
                INNER JOIN tuitionSubmissions ts ON
                    ts.id = rs.id
                /*Prev join condition should suffice, but add if needed*/
                --AND ts.EmpID = rs.EmpID 
      )
SELECT  A.[empID],
/*      ... And the rest of your query as is.
        ... Just substitute tuitionSubmissions with UniqueSubs.
*/

TSQL Distinct in Select Statement with Joins

2 Answers2