Search code examples
sqlsqliteduplicatesmask

SQLITE select mask, how to include duplicates as weel

I have a query like this and I would like to return all the values in the IN case with the matching number of rows.

SELECT id,name,age from USERS WHERE id IN (1,75,75);

returns

1|john|25

75|Sam|30

however what I want is

1|john|25

75|Sam|30

75|Sam|30

is such a thing possible in sql? I would appreciate if you guys have a solution for this.

Thanks

Solution

  • You can use a join instead:

    select u.*
from (select 1 as id union all select 75 union all select 75) i join
     users u
     on u.id = i.id;

    The more concise format uses values():

    with i(id) as (
      values (1), (75), (75)
)
select u.*
from i join
     users u
     on u.id = i.id;