Duplicate rows with SELECT with WHERE IN

Question

I have a problem with building a query that would get me unique rows. The situation is:

I have a TABLE product like this:

id   name    price
==================
1    bolt    50
2    screw   4
3    hammer  40
4    drill   30

and a TABLE products2tags like this:

id   id_product  id_tag
=======================
1    1           1
2    2           1
3    2           2
4    2           3
5    3           3

In my app, I am rendering a list with all products there are and I am building a filter where a user should be able to select zero or many tags and would get a list of all products that have given tag assigned (there is a row with that product ID and tag ID in table products2tags).

A query

SELECT *
FROM products AS p
   JOIN products2tags AS p2t
      ON p.id = p2t.id_product
   WHERE IN p2t.id_tag in (1, 3);

is working when I select only one tag in my filter (therefore there is one tag ID in the WHERE statement. When I select more tags, I still get a result, but I get some of the rows multiple times - the ones that have multiple tags assigned.

All I need is to get unique rows when selecting more than one tag.

EDIT:

Expected result of the query from the tables above:

p.id   p.name   p.price   p2t.id   p2t.id_product   p2t.id_tag
==============================================================
1      bolt     50        1        1                1
2      screw    4         2        2                1         (only once)
4      drill    30        5        4                3

No, I need every product that has id tag 1 or 3. So for example product with tag id 1,2,4,7,13 should be fetched. Same as with 3,6,100 and also with 1,3,4 but only once, my query gets me this products twice because it has both tag id 1 and 3. — Michal Kroupa, Jan 31 '20 at 19:23
These ids are not all in your sample data. You have products with ids 1,2,3,4. Also Andrew O'Brien's answer returns exactly what you want (if you correct syntactical errors): https://www.db-fiddle.com/f/fWYxNWYT4ZBhXq7isAskMm/1 — forpas, Jan 31 '20 at 19:34
When more than one row matches the conditions of the were clause then there are multiple possible values for `p2t.id_tag`. In the example give `p2t.id_tag` is 1 for one row of data and 3 for another. What is the rule to determine which value to show on the single line of the result? Same question for `p2t.id`. — Shannon Severance, Jan 31 '20 at 19:35
@forpas You are right, it is working with these sample data I provided but not in my actual app. — Michal Kroupa, Jan 31 '20 at 19:47
So if you do not explain your requirement correctly how do you expect to get a solution? — forpas, Jan 31 '20 at 19:50
I haven't expected that it would made such difference, my bad. — Michal Kroupa, Jan 31 '20 at 20:12
You still don't say how desired output is a function of input. This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: Say enough that someone could go away & come back with a solution. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. See [ask], other [help] links & the voting arrow mouseover texts. PS Please clarify via edits, not comments. Please delete/flag unneeded comments. — philipxy, Jan 31 '20 at 23:17
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) [ask] For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. — philipxy, Jan 31 '20 at 23:19

score 1 · Answer 1 · answered Jan 31 '20 at 18:29

1

This is occurring because you are including the tags in your result set. Try

SELECT DISTINCT
    product.*
FROM products AS p
   JOIN products2tags AS p2t
      ON p.id = p2t.id_product
   WHERE IN p2t.id_tag in (3, 4);

answered Jan 31 '20 at 18:29

Andrew O'Brien

1,793
1
12
24

That will get me only products that have tag with ID 3 or tag with ID 4, not those that have both 3 and 4 or even 3 and any number of other IDs – Michal Kroupa Jan 31 '20 at 18:38
This will get you products that have either tag 3 or tag 4. This is the where clause you provided. You are welcome to update your where clause to whatever condition you want. It's the columns selected and the DISTINCT hint that ensure you return distinct results. – Andrew O'Brien Feb 04 '20 at 23:00

zip · Answer 2 · 2020-01-31T19:59:02.137

0

This will give you products ids with the tags selected:

SELECT p.id
FROM products AS p
   JOIN products2tags AS p2t
      ON p.id = p2t.id_product
   WHERE p2t.id_tag in (1, 3)
   group by p.id
   having count(*) = (select count(distinct id_tag) 
                      from products2tags where id_tag in (1, 3)
                     );

Just get the product that have one of the tag id in the list without duplication:

SELECT distinct p.*
FROM products AS p
   JOIN products2tags AS p2t
      ON p.id = p2t.id_product
   WHERE p2t.id_tag in (1, 3);

edited Jan 31 '20 at 19:59

answered Jan 31 '20 at 18:47

zip

3,938
2
11
19

That got me only one result and it should get me 3. – Michal Kroupa Jan 31 '20 at 18:58
Mickal, looking at your example only the product 2 has the tags 1 and 3. isnt what you wanted? – zip Jan 31 '20 at 19:04
No, I need every product that has id tag 1 or 3. So for example product with tag id 1,2,4,7,13 should be fetched. Same as with 3,6,100 and also with 1,3,4 but only once, my query gets me this products twice because it has both tag id 1 and 3. – Michal Kroupa Jan 31 '20 at 19:10
Added another solution highly inspired by your query at the end of my answer – zip Jan 31 '20 at 19:59

score 0 · Answer 3 · answered Feb 01 '20 at 02:51

0

Use distinct on p.id.

select distinct on (p.id) *
from products as p
   join products2tags as p2t
      on p.id = p2t.id_product
   where p2t.id_tag in (1, 3);

answered Feb 01 '20 at 02:51

Belayer

13,578
2
11
22

Duplicate rows with SELECT with WHERE IN

3 Answers3