CODEWITHSUNDEEP
sql-serverdatabaseduplicatesunion
LeoStotch
LeoStotch

Reputation: 125

How can I prevent getting duplicates with this UNION select (Or a different method)

I am trying to get a certain set of results with a UNION SELECT, and I am having trouble figuring out how to limit my results accordingly. Essentially I have objects that I am returning and displaying in the form of several of their attributes. One of those attributes is a comment that can be made on the object. Some have comments on them, and some do not. I want to display all that have comments as well as all that do not have comments. The problem I am running into: My first SELECT is returning all of the instances with comments just fine, but my second SELECT which is meant to return the instances without comments, not only does that, but also returns a duplicate instance of the previous, but without a comment. So in the end I have all instances where there is no comment, and if there is a comment, I get in instance with the comment and a duplicate but without the comment. I have pasted my code below, but if you feel there is a better way of doing this other than using a union select, your enlightenment is much appreciated.

Some Background: In my DB, I have my main table of 'Deals' and a Deal can have many comments, and I am using the DealID with a marker to tell if that Comment is active or not.

My Stored Procedure:

 @Dealership nvarchar(50)

AS

    SELECT DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName,
    Comment 
FROM
    tVehicleDeal,  
    tDealerships, 
    tInternalLocations,
    tVehicleComments,
    tCustomer
WHERE 
(tVehicleDeal.DealershipID = tDealerships.DealershipID) 
AND (tDealerships.DealershipName LIKE '%'+@Dealership+'%')
AND tVehicleDeal.InternalLocationID = tInternalLocations.InternalLocationID
AND tVehicleDeal.DealID = tVehicleComments.DealID
AND tVehicleDeal.CustomerID = tCustomer.CustomerID
AND NOT tInternalLocations.LocationName = 'Down Deal' 
AND tVehicleDeal.Titled IS NULL
AND tVehicleDeal.Archived = 0 

    UNION SELECT DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName,
    NULL
FROM

    tVehicleDeal,  
    tDealerships, 
    tInternalLocations,
    tCustomer

WHERE 
(tVehicleDeal.DealershipID = tDealerships.DealershipID) 
AND (tDealerships.DealershipName LIKE '%'+@Dealership+'%')
AND tVehicleDeal.InternalLocationID = tInternalLocations.InternalLocationID
AND tVehicleDeal.CustomerID = tCustomer.CustomerID
AND NOT tInternalLocations.LocationName = 'Down Deal' 
AND tVehicleDeal.Titled IS NULL
AND tVehicleDeal.Archived = 0 

ORDER BY [DealDate] ASC

-Thanks in advance!

Upvotes: 1

Views: 80

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

You need to read up on the SQL-92 Join syntax. You need to use an Outer Join to the comments table.

try this:

SELECT DealDate, DateReceived, Bounced, StockNumber, 
   LocationName, CustomerName, Comment 
FROM tVehicleDeal vd
   Join tDealerships d 
      On d.DealershipID = vd.DealershipID
        And d.DealershipName LIKE '%'+@Dealership+'%'
   Join tInternalLocations il 
      On il.InternalLocationID = vd.InternalLocationID
        And il.LocationName <> 'Down Deal' 
   Join tCustomer cu 
      On cu.CustomerID = vd.CustomerID
   Left Join tVehicleComments c 
      On c.DealID = vd.DealId
WHERE vd.Titled Is Null
   And vd.Archived = 0

Upvotes: 1

StuartLC
StuartLC

Reputation: 107267

Can I suggest that you

  • Use JOIN syntax instead of the WHERE
  • Use a LEFT OUTER JOIN to tVehicleComments

and this way you should be able to avoid the repeated query with the UNION entirely, e.g.

Edit
If you potentially have more than one comment per vehicledeal, you can restrict the output to just one of them by grouping by the remainder of columns and applying an appropriate Aggregation function to the comment, e.g.:

SELECT 
    DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName,
    MIN(Comment) AS FirstComment
FROM
    tVehicleDeal 
    INNER JOIN tDealerships
      ON tVehicleDeal.DealershipID = tDealerships.DealershipID
    INNER JOIN tInternalLocations,
      ON tVehicleDeal.InternalLocationID = tInternalLocations.InternalLocationID, 
    LEFT OUTER JOIN tVehicleComments 
      ON tVehicleDeal.DealID = tVehicleComments.DealID
    INNER JOIN tCustomer 
      ON tVehicleDeal.CustomerID = tCustomer.CustomerID
WHERE 
  tDealerships.DealershipName LIKE '%'+@Dealership+'%'
  AND NOT tInternalLocations.LocationName = 'Down Deal' 
  AND tVehicleDeal.Titled IS NULL
  AND tVehicleDeal.Archived = 0 
GROUP BY
    DealDate,
    DateReceived,
    Bounced,
    StockNumber,
    LocationName,
    CustomerName;

Upvotes: 0

RyanB
RyanB

Reputation: 757

If the results on both sides aren't EXACTLY the same you'll get both. Since you have the NULL in place of Comment in the second query, the result for that row is different from the result in the first query and thus you get both results.

One way to fix it would be to add a 

AND Comment IS NULL

to the second query.

Upvotes: 1

Related Questions