CODEWITHSUNDEEP
sqldistinct-values
George Girgolas
George Girgolas

Reputation: 3

Sql return distinct rows with specific values

I have one table like 

product_id      tag_id    value

1                 1         10
1                 2         51
1                 3         47
2                 1         15
2                 2         59
2                 3         44
3                 1         10
3                 2         51
3                 3         47
4                 1         10 
4                 2         12
4                 3         55

I want to create query that returns distinct product id's that meets specific criterias from ALL three tag id's.

For example i want the product id's that has tag_id 1 = 10 and tag_id 2 = 51 and tag_id 3 = 47.

Thnks

Upvotes: 0

Views: 123

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460208

If you have not endless possible combinations i would prefer following natural approach which is also maintainable (if conditions are getting more complex):

SELECT DISTINCT product_id
FROM dbo.TableName t
WHERE EXISTS
(
   SELECT 1 FROM dbo.TableName t1
    WHERE t1.product_id = t.product_id
    AND   t1.tag_id = 1 
    AND   t1.Value = 10
)
AND EXISTS
(
    SELECT 1 FROM dbo.TableName t1
    WHERE t1.product_id = t.product_id
    AND   t1.tag_id = 2 
    AND   t1.Value = 51
)
AND EXISTS
(
    SELECT 1 FROM dbo.TableName t1
    WHERE t1.product_id = t.product_id
    AND   t1.tag_id = 3
    AND   t1.Value = 47
)

Upvotes: 0

Backs
Backs

Reputation: 24913

To reduce mistakes I prefere to descripe such conditions in separate table and join to target table:

DECLARE @Products TABLE (product_id int, tag_id int, value int)

INSERT INTO @Products
VALUES
(1,                 1,         10),
(1,                 2,         51),
(1,                 3,         47),
(2,                 1,         15),
(2,                 2,         59),
(2,                 3,         44),
(3,                 1,         10),
(3,                 2,         51),
(3,                 3,         47),
(4,                 1,         10),
(4,                 2,         12),
(4,                 3,         55)

DECLARE @Conditions TABLE (tag_id int, value int)
INSERT INTO @Conditions
VALUES
(1, 10),
(2, 51),
(3, 47)


SELECT p.product_id
FROM @Products p
INNER JOIN @Conditions c ON p.tag_id = c.tag_id AND p.[value] = c.[value]
GROUP BY p.product_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Conditions c2)

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

This is usually done using HAVING:

SELECT product_id
FROM tablename
WHERE (tag_id = 1 AND value = 10)
   OR (tag_id = 2 AND value = 51)
   OR (tag_id = 3 AND value = 47)
GROUP BY product_id
   HAVING COUNT(*) = 3 -- number of searched combinations

This assumes that the combinationtag_id/value is unique, otherwise you have to change the count to add a distinct like COUNT(DISTINCT tag_id)

Upvotes: 1

jarlh
jarlh

Reputation: 44786

Either do a GROUP BY, use HAVING to make sure all 3 different tag_id/value combos are included.

SELECT product_id
FROM tablename
WHERE (tag_id = 1 AND value = 10)
   OR (tag_id = 2 AND value = 51)
   OR (tag_id = 3 AND value = 47)
group by product_id
having count(distinct tag_id) >= 3

Or, do a double self join:

select distinct t1.product_id
from
    (select product_id from tablename where tag_id = 1 AND value = 10) t1
join
    (select product_id from tablename where tag_id = 2 AND value = 51) t2 on t1.product_id = t2.product_id
join
    (select product_id from tablename where tag_id = 3 AND value = 47) t3 on t2.product_id = t3.product_id

Upvotes: 0

Related Questions