SQL: Problems with IN clause, how to return for ALL values, even duplicates

I want:

SELECT someID FROM table1 WHERE someID IN (1,1,2,2,3) 

to return

someID
1
1
2
2
3

while it now return only distinct values from the IN clause, like this:

someID
1
2
3

I use Micros开发者_运维问答oft SQL Server and my table contains a unique someID and PLSRec.

CREATE TABLE [dbo].[table1](
    [someID] [int] IDENTITY(1,1) NOT NULL,
    [PLSRec] [int] NOT NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(

How about something like?:

DECLARE @table2 table(someID int)

INSERT INTO @table2(someID) VALUES (184),(132);

SELECT * FROM @table2 LEFT JOIN table1 ON @table2.someID=table1.someID

(except the INSERT INTO-sentence returns "Incorrect syntax near ',')

This can only be accomplish by a Cartesian product, a.k.a. JOIN. In this case, INNER JOIN. You need to make a rowset containing all those values, like this:

SELECT 1 as someID
UNION ALL SELECT 1 as someID
UNION ALL SELECT 2 as someID
[...]

Then, using it in a JOIN clause (with CTEs if you're using MS SQL)

SELECT table1.someID FROM table1
JOIN (SELECT 1 as someID
UNION ALL SELECT 1 as someID
UNION ALL SELECT 2 as someID
[...]
) as table2 on table1.someID=table2.someID

Of course, this IS an extremely ugly solution.

It will only check the row's someID against the values of the IN().

It doesn't matter how your IN() looks like, it will not return more results than there are rows that matches that value.

I think you have misunderstood something. Your code is mere syntactic sugar for

SELECT someID 
  FROM table1 
WHERE (
       someID = 1 
       OR someID = 1 
       OR someID = 2 
       OR someID = 2 
       OR someID = 3
      );

Your "IN clause" is not a set: if you want it to be then use a table e.g. CTE, inline derived table, static VIEW, table variable, etc. But note that a set does not have duplicate rows!

I haven't tried it but

SELECT someID, count(someID) FROM table1 WHERE someID IN (1,2,3) group by someID

may serve your purpose.

Or

SELECT someID id1, someID id2 FROM table1 WHERE id1 IN (1,2,3) 

might work

EDIT: Which database? I am using Sybase SQLAnywhere and it does return multiple rows.