mysql "where in" automatic distinct?

Question

I am a bit lost. Here is my query:

SELECT id, title FROM products WHERE id IN (4,2,6,4,2);

The query will result in 3 records only.

How can I get all 5 records (in this case I do want to see the duplicates).

Many thanks.

Update: Sorry for not being exact enough. Thanks for the comments, indeed I didn't understand the "IN" correctly.

This is my data:

---------------------
|  id |  title      |
---------------------
|  2  |  product_2  |
---------------------
|  4  |  product_4  |
---------------------
|  6  |  product_6  |
---------------------

My intention is to receive the following data since I have a list of ids:

---------------------
|  id |  title      |
---------------------
|  4  |  product_4  |
---------------------
|  2  |  product_2  |
---------------------
|  6  |  product_6  |
---------------------
|  4  |  product_4  |
---------------------
|  2  |  product_2  |
---------------------

So I have a list of ids and want to receive the title of the product. Ideally even the order should be preserved.

What "all 5 records"? `WHERE IN (42)` equals to `WHERE IN (42,42,42,42,42)` — zerkms, Sep 01 '12 at 21:56

score 2 · Answer 1 · answered Sep 01 '12 at 22:02

2

I think you have a problem with the concept of In

Let's suppose you have a simply table with this values

id
--
1
2
3
4
5

if you do

select id from simplyTable where id in (2,3,4,2,3,4)

you will get

id
2
3
4

That's becuase IN statement in where is a filter, to decide to show or not a row. If you put repeated values, SQL will ignore that, because when finds the first value, and checks the match, it will show the row, and won't keep checking against the other values So, you need to put once time each number, and if you put it repeated, it won't show them more times.

If you need to repeated some values, a way to do this is Use UNION ALL

select id from simplyTable where id in (2,3,4)
union all
select id from simplyTable where id in (2,3,4)

this will return you:

don't use UNION only because it will apply a distinct, and will return you only one time each value. UNION will let you show the values repeated, because you're showintg results of querys i one result, and I think that's what you're expecting.

In this case, your query will be:

SELECT id, title FROM products WHERE id IN (4,2,6);
UNION ALL
SELECT id, title FROM products WHERE id IN (4,2)

answered Sep 01 '12 at 22:02

Gonzalo.-

12,512
5
50
82

Many thanks for your answer. So it would be quite tedious if I have a list with 50 entries, as I have to remove the duplicates first for the second SELECT statement? – Elvira Sep 01 '12 at 22:11
depends - show us an example of your data and the desired output, and I will be able to answer your doubt – Gonzalo.- Sep 01 '12 at 22:26
Thank you, I updated my question. Basically I have a list of ids and want the title (even the duplicate). Maybe "IN" isn't the best choice here but I wouldn't know how to replace it. – Elvira Sep 01 '12 at 22:30
that's is your result, but what is your real stored data? because if that was your data stored, your query could have worked – Gonzalo.- Sep 01 '12 at 22:35
Sorry, yes it is also my data. But my query, doesn't result in the desired output. – Elvira Sep 01 '12 at 22:39
@Elvira in this case, yes, you will have to do this unions. That's because the data only exists on your table once, and if you want to show it more times, you will have to specify it to the engine – Gonzalo.- Sep 02 '12 at 16:43

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

Join with Temporary Table

SqlFiddle:

DROP   TABLE IF EXISTS num;
CREATE TABLE num (i INT);    
INSERT INTO  num (i) VALUES (4),(2),(6),(4),(2);

SELECT id, title
FROM   num, products
WHERE  id = i;

Inspired by another answer.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 02 '12 at 01:33

mmdemirbas

9,060
5
45
53

mysql "where in" automatic distinct?

2 Answers2

Join with Temporary Table