how to get duplicate values from a mysql query on input of array?

Question

I have a query for which I am giving a list of items as input, the item list contains duplicate values.

select * from ITEM  where ITEM_NUMBER in ('15','41', '15','41');

the resulting output contains 2 rows with

ITEM_NUMBER 15

and

41

I want a query, which returns 4 rows with duplicate values.

Answer 1

This is one option using join:

select *
from items a
join (select item_number
      from items  
      group by  item_number
      having count(*) > 1) b
on a.item_number = b.item_number;

Adapted from an answer in this link: Show all duplicated rows

Answer 2

Here's a solution using UNION and TOP

select * from 
(select top 2 * from item where item_number=15) 
union all select * from 
(select top 2 * from item where item_number=41);