In MySql how do I get row duplication for the case where I pass duplicate IDs in a list?

Question

For this MySQL SELECT statement:

SELECT * FROM MY_TABLE WHERE ID IN(x,y,y,z):

I want 4 rows back - ie I WANT row duplication for the case where I pass duplicate IDs in the list.

Is this possible?

Answer 1

SELECT * FROM MY_TABLE WHERE ID IN(x,y,z)
union all 
SELECT * FROM MY_TABLE WHERE ID IN(y)

Answer 2

using the IN() construct, that's not possible.

the only way i can think to do this is with a UNION:

SELECT * FROM my_table WHERE id = x
UNION ALL
SELECT * FROM my_table WHERE id = y
UNION ALL
SELECT * FROM my_table WHERE id = y
UNION ALL
SELECT * FROM my_table WHERE id = z

but in all honesty, i would just do the IN() like you have it and make your app code duplicate the rows as needed.

Answer 3

To me, IN specify a set of values to search in (and duplication is a concept that conflict with the set one).

You should use other mean to reach your scope.

Answer 4

Put your IDs, including dups in a temp table and join your results on that table. The join will take care of filtering, but will keep duplicates if it's in the temp table twice