SQL: Need To Remove Duplicate Rows In Query Containing Multiple Joins

Note that I'm a complete SQL noob and in the process of learning. Based on Google searches (including searching here) I've tried using SELECT DISTINCT and GROUP BY but neither wor

Solution 1:

If everything "works fine" until you add:

LEFT JOIN tb_Session SES
ON SES.ClockSerialNumber = CLK.SerialNumber

Then there must be more than one record in tb_Session for each CLK.SerialNumber.

Run the following query:

SELECT  *
FROM    tb_Session SES
WHERE   ClockSerialNumber = '08-107'

There should be two records returned. You need to decide how to handle this (i.e. Which record do you want to use?), unless both rows from tb_Session contain identical data, in which case, should they?

You could always change your query to:

SELECT
    CLT.Description AS ClockType,
    CLK.SerialNumber AS JobClockSerial,
    SIT.SiteNumber AS JobID,
    SIT.[Name] AS JobsiteName,
    SIT.Status AS SiteActivityStatus,
    DHA.IssuedDate AS DHAIssuedDate, -- Date the clock was assigned to THAT jobsite
    CLK.CreatedDate AS CLKCreatedDate, -- Date clock first was assigned to ANY jobsite
    SES.ClockVoltage
FROM tb_Clock CLK
INNER JOIN tb_ClockType CLT
ON CLK.TypeID = CLT.ClockTypeID
INNER JOIN tb_DeviceHolderActivity DHA
ON CLK.ClockGUID = DHA.DeviceGUID
INNER JOIN tb_Site SIT
ON SIT.SiteGUID = DHA.HolderGUID
LEFT JOIN 
(
    SELECT DISTINCT ClockSerialNumber, ClockVoltage
    FROM tb_Session 
) SES
ON SES.ClockSerialNumber = CLK.SerialNumber
WHERE DHA.ReturnedDate IS NULL
ORDER BY SIT.[Name] ASC

As that should ensure that SES only contains one record for each unique combination of ClockSerialNumber and ClockVoltage

Solution 2:

Take this example dataset:

Ingredient

IngredientId IngredientName
============ =========
1            Apple
2            Orange
3            Pear
4            Tomato

Recipe

RecipeId RecipeName
======== ==========
1        Apple Turnover
2        Apple Pie
3        Poached Pears

Recipe_Ingredient

RecipeId IngredientId Quantity
======== ============ ========
1        1            0.25
1        1            1.00
2        1            2.00
3        3            1.00

Note: Why the Apple Turnover has two lots of apple as ingredients, is neither here nor there, it just does.

The following query will return two rows for the "Apple Turnover" recipe, one row for the "Apple Pie" recipe and one row for the "Poached Pears" recipe, because there are two entries in the Recipe_Ingredient table for IngredientId 1. That's just what happens with a join..

SELECT  I.IngredientName,
        R.RecipeName
FROM    Ingredient I
JOIN    Recipe_Ingredient RI
        ON I.IngredientId = RI.IngredientId
JOIN    Recipe R
        ON RI.recipeId = R.RecipeId

You could get this to return only one row by changing it to:

SELECT  I.IngredientName,
        R.RecipeName
FROM    Ingredient I
JOIN    Recipe_Ingredient RI
        ON I.IngredientId = RI.IngredientId
JOIN    Recipe R
        ON RI.recipeId = R.RecipeId
GROUP BY I.IngredientName, R.RecipeName

Without more specifics regarding your data, it's hard to apply this to your specific scenario, but the walkthrough may help you understand where the "duplicates" are coming from as someone unfamiliar with SQL

Solution 3:

The joins are not your problem. From your comments I will infer that what you are calling "duplicates" are not actual duplicates. If all columns values for 2 "duplicates" returned from the query matched, then either SELECT DISTINCT or GROUP BY would definitely eliminate them. So you should be able to find a solution by looking at your column definitions.

My best guess is that you're getting duplicates of for the same date which aren't really duplicates because the time component of the date doesn't match. To eliminate this problem, you can truncate the date fields to the date only using this technique:

    DATEADD(DAY, DATEDIFF(DAY, 0, DHA.IssuedDate), 0) AS DHAIssuedDate,
    DATEADD(DAY, DATEDIFF(DAY, 0, CLK.CreatedDate), 0) AS CLKCreatedDate,   

If that doesn't work you might want to take a look at JobClockSerial: does this column belong in the query results?