How do I apply the Microsoft SQL IN operator simultaneously pairwise?

Question

I have a table in Microsoft SQL called Account

Name    AccountNumber    IDNumber
Alpha   1                Z
Bravo   2                Y
Charlie 3                X
Delta   4                W
Echo    5                W   <---- Echo has same IDNumber as Delta. This is allowed.

I have a list of unknown length of pairs of AccountNumber and IDNumber. Only accounts where the pair match should be returned.

Here is an example of such a list (AccountNumber, IDNumber)

(2,Y) <-- Valid
(4,W) <-- Valid
(1,X) <-- Invalid
(5,X) <-- Invalid
(5,W) <-- Valid

Is it possible to select only such valid pairs?

My attempt

For testing purposes I am using this table and data.

DROP TABLE #Account
CREATE TABLE #Account (
[Name] NVARCHAR(100),
[AccountNumber] NVARCHAR(10),
[IDNumber] NVARCHAR(10)
)

INSERT INTO #Account ([Name],[AccountNumber],[IDNumber]) VALUES ('Alpha' ,'1', 'Z')
INSERT INTO #Account ([Name],[AccountNumber],[IDNumber]) VALUES ('Bravo' ,'2', 'Y')
INSERT INTO #Account ([Name],[AccountNumber],[IDNumber]) VALUES ('Charlie' ,'3', 'X')
INSERT INTO #Account ([Name],[AccountNumber],[IDNumber]) VALUES ('Delta' ,'4', 'W')
INSERT INTO #Account ([Name],[AccountNumber],[IDNumber]) VALUES ('Echo' ,'5', 'W')

This correctly returns the accounts.

SELECT * FROM #Account
WHERE AccountNumber IN ('2','4','1') AND IDNumber IN ('Y','W','X')


Name    AccountNumber   IDNumber
Bravo   2   Y
Delta   4   W

But this is incorrect

SELECT * FROM #Account
WHERE AccountNumber IN ('4','5') AND IDNumber IN ('W','X')


Name    AccountNumber   IDNumber
Delta   4   W
Echo    5   W

I need 4-W paired and 5-X paired.

(My aim is to do this from C# using Linq. As a first attempt I would like to see if this is doable in SQL.)

If I am not wrong, we can use string concatenation. Select * from table where concat(AccountNumber,IDnumber) in (select concat(accountnumber,idnumber) from pair_table). I don't know if there is any disadvantage using this. Also, we can use where exists with the tables. — T.kowshik Yedida, Feb 27 '21 at 13:53
Well, if you need to know if its is possible in SQL, then don't ask for LINQ / EF Core. Because for sure it is possible in SQL, but that's not the case with ORMs / LINQ translators like current EF Core which cannot translated/pass /perform joins with in-memory collection of non primitive (i.e. single) values. Hence remove the `linq` and `entity-framework-core` tags and see/accept what Sql (Server) experts say. But it won't help you implementing it in EF Core exception if you build dynamically raw SQL. — Ivan Stoev, Feb 27 '21 at 14:59
See https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter — Charlieface, Feb 27 '21 at 21:12

score 1 · Accepted Answer · answered Feb 27 '21 at 13:40

1

Use values to generate a list and then join:

SELECT a.*
FROM #Account a JOIN
     (VALUES ('2', 'Y'), ('4', 'W'), ('1', 'X')
     ) v(AccountNumber, IDNumber)
     ON a.AccountNumber = v.AccountNumber AND
        a.IDNumber = v.IDNumber;

answered Feb 27 '21 at 13:40

Gordon Linoff

1,242,037
58
646
786

can we use it dynamically? As in using a select statement inside the Values. – T.kowshik Yedida Feb 27 '21 at 13:51
1

@T.kowshikYedida, to pass rows/columns as a parameter for use in queries, you could use a table-valued parameter, JSON, or XML. – Dan Guzman Feb 27 '21 at 14:10

How do I apply the Microsoft SQL IN operator simultaneously pairwise?

1 Answers1