CODEWITHSUNDEEP
sqlselectunion
Louisa
Louisa

Reputation: 550

SQL - filtering the results of a union

Have a union like this:

(SELECT prod.id, price, timestamp
FROM products prod JOIN prices price ON prod.id = price.productid
WHERE price > 0
GROUP BY prod.id, DATE(timestamp))
UNION
(SELECT prod.id, price, timestamp
FROM products prod JOIN prices price ON prod.id = price.productid
WHERE price = 0
GROUP BY prod.id, DATE(timestamp))

The problem:

If there are dates where a product is available with price = 0 and price > 0, we get two rows in the union result.

What we want is for the price = 0 result to be ignored if there is a price > 0 result for the same product on the same date.

Anyone who can share a trick for fixing this?

Upvotes: 1

Views: 68

Answers (2)

Hopper
Hopper

Reputation: 146

are you trying to get the "have a price" to be grouped separately from the "only have a 0 price"? if so then put the first group into a temp table do the select for the second group where the product ID isn't in the temp table.

CREATE TABLE #HASPRICE (prod.id INT, price Decimal, timestamp DATE)

INSERT INTO #HASPRICE
SELECT prod.id, price, timestamp
FROM products prod JOIN prices price ON prod.id = price.productid
WHERE price > 0
GROUP BY prod.id, DATE(timestamp)

INSERT INTO #HASPRICE
SELECT prod.id, price, timestamp
FROM products prod JOIN prices price ON prod.id = price.productid
WHERE price = 0 AND prod.id not in (select prod.id from #HASPRICE)
GROUP BY prod.id, DATE(timestamp))

if not, then this will work

(SELECT prod.id, price, timestamp
FROM products prod JOIN prices price ON prod.id = price.productid
WHERE price >= 0
GROUP BY prod.id, DATE(timestamp))

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

I wouldn't call it a "trick". Just add a condition to the WHERE clause of the price = 0 side of the UNION that filters out products that also have a price > 0.

I'd do it with a NOT EXISTS() function myself, but there are several other ways to do this.

Upvotes: 1

Related Questions