user1032531 user1032531 - 1 year ago 67
SQL Question

Returing all records including duplicated with MySQL IN clause

Assuming both

equal to 1 and 3 exists in the table, the following query will return two records.

SELECT fk, name FROM my_table WHERE fk IN (1,3,1);

How can I instead return three records:

fk name
1 john
3 mary
1 john

Answer Source

You need to use a join instead:

from my_table t join
     (select 1 as x union all select 3 union all select 1
     ) vals
     on = vals.x;

If you also want to keep the ordering, then use a second column -- the order of the rows after the join is not guaranteed unless you include an order by:

from my_table t join
     (select 1 as x, 1 as ordering union all
      select 3, 2 union all
      select 1, 3
     ) vals
     on = vals.x
order by vals.ordering;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download