CODEWITHSUNDEEP
sqlsql-serversql-server-2008azure-sql-database
MJJLAM
MJJLAM

Reputation: 183

Filtering Duplicates from UNION results based on 2 columns

I am getting the UNION of two select queries, one of the tables being queried is a temp table to ensure that records are outputted (so the temp table has the expected output for each date but with a default value of 0 for the Sale column

An example output result would be something like this:

ProductID  | Product Desc | TransactionDate | Sale
1011021    | SD DOG       | 2017-01-07      | 0
1011021    | SD DOG       | 2017-01-07      | $17
1011021    | SD DOG       | 2017-01-14      | 0
1011021    | SD DOG       | 2017-01-14      | $15
1011021    | SD DOG       | 2017-01-21      | $21
1011021    | SD DOG       | 2017-01-28      | 0
1011021    | SD DOG       | 2017-01-28      | $21

Union removes duplicates based on rows, how can I make it remove duplicates based on the productID and transactionDate column, removing the duplicates where Sale is 0?

--Query below

SELECT transactionProducts.productID, products.productDesc, sum(salePrice) as Sale, transactionDate 
FROM products LEFT JOIN transactionProducts on products.productID = transactionProducts.productID 
LEFT JOIN transactions ON transactionProducts.transactionID = transactions.transactionID 
LEFT JOIN productCategory on productCategory.productID = products.productID 
LEFT JOIN categories on categories.categoryID = productCategory.categoryID
WHERE (transactionProducts.productID='123' AND transactions.transactionDate='2017-01-12'
Group by transactionProducts.productID, transactionDate
UNION select * from "temptable" group by productID, productDesc, Sale, transactionDate 
ORDER BY transactionDate

Upvotes: 2

Views: 2788

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use not exists to select the rows from the temporary table that are not already in the result set:

. . .
UNION ALL
SELECT tt.*
FROM temptable tt
WHERE NOT EXISTS (SELECT 1
                  FROM transactionproducts tp JOIN
                       transaction t
                       ON t.transactionID = tp.transactionID
                  WHERE tp.productId = tt.productId AND
                        t.transactionDate = tt.transactionDate
                 )
GROUP BY productID, productDesc, Sale, transactionDate 
ORDER BY transactionDate

Upvotes: 3

andrews
andrews

Reputation: 2173

How about this:

SELECT transactionProducts.productID, products.productDesc, sum(salePrice) as Sale, transactionDate 
FROM products LEFT JOIN transactionProducts on products.productID = transactionProducts.productID 
LEFT JOIN transactions ON transactionProducts.transactionID = transactions.transactionID 
LEFT JOIN productCategory on productCategory.productID = products.productID 
LEFT JOIN categories on categories.categoryID = productCategory.categoryID
WHERE (transactionProducts.productID='123' AND transactions.transactionDate='2017-01-12' 
          AND sum(salePrice) > 0 )
Group by transactionProducts.productID, transactionDate
UNION select * from "temptable" group by productID, productDesc, Sale, transactionDate 
ORDER BY transactionDate

Note, I've added " AND sum(salePrice) > 0 " to the first SELECT WHERE clause.

HTH

Upvotes: 0

Related Questions