Return All For Where In (1,2,3,3,3,1) Clause With Duplicates In The In Condition

I need to return all values for: select...where IN (1,2,3,3,3,1) I have a table with unique IDs. I have the following query: Select * From Table_1 Where ID IN (1,2,3,3,3,1); So f

Solution 1:

You cannot do this using the IN condition, because IN treats your items as a set (i.e. ensures uniqueness).

You can produce the desired result by joining to a UNION ALL, like this:

SELECT t.*FROM Table_1 t
JOIN ( -- This is your "IN" listSELECT1AS ID
UNIONALLSELECT2AS ID
UNIONALLSELECT3AS ID
UNIONALLSELECT3AS ID
UNIONALLSELECT3AS ID
UNIONALLSELECT1AS ID
) x ON x.ID = t.ID

Solution 2:

You can't do that with the IN operator. You can create a temporary table and JOIN:

CREATETABLE #TempIDs
(
ID int
)

INSERTINTO #TempIDs (1)
INSERTINTO #TempIDs (2)
INSERTINTO #TempIDs (3)
INSERTINTO #TempIDs (3)
INSERTINTO #TempIDs (3)
INSERTINTO #TempIDs (1)

Select Table_1.*From Table_1
INNERJOIN #TempIDs t n Table_1.ID = t.ID;

Another (maybe uglier) option is to do a UNION:

Select*From Table_1 Where ID =1UNIONALLSelect*From Table_1 Where ID =2UNIONALLSelect*From Table_1 Where ID =3UNIONALLSelect*From Table_1 Where ID =3UNIONALLSelect*From Table_1 Where ID =3UNIONALLSelect*From Table_1 Where ID =1

Solution 3:

You can do it. But not with IN.

Select 
src.*From Table_1 src
innerjoin (
 select
ID,
myorder
from (values
 (1,0),
(2,1),
(3,2),
(3,3),
(3,4),
(1,5)
) x (ID,myorder)
) T ON
T.ID = src.ID
orderby T.myorder

Keep in mind if you want your dataset Ordered you have to supply the order by clause.