How to get multiple rows if where clause has repeated condition values in SQL

0
votes

I am trying to fetch all the rows using WHERE IN (...ids) format.This is working fine. But if ids inside IN are repeated only one row for that ID is returned by default by SQL.

Is there a way i can get same row N times if the ID is present N times inWHERE IN (...ids)?

Ex: Data in Table

id name
1 AAA
2 BBB
3 CCC

Query SELECT * FROM people WHERE id IN (1, 2, 2, 3);

Result

id name
1 AAA
2 BBB
3 CCC

Expected Result

id name
1 AAA
2 BBB
2 BBB
3 CCC

Is there a way to get this result in SQL?

I am using Postgres.

Thank you

| 3 | CCC |

1
sqlpostgresql

1 Answers

1
votes

WHERE clauses do not -- as a general rule -- change the number of rows. Instead, use JOIN:

SELECT p.*
FROM (VALUES (1), (2), (2), (3)) v(id) JOIN
     people p
     USING (id);

Note: If you want all ids in the list, even those not in people use LEFT JOIN.

If you want to pass in the values as a parameter, then arrays are more convenient than VALUES():

SELECT p.*
FROM UNNEST(ARRAY[1, 2, 2, 3]) v(id) JOIN
     people p
     USING (id);