CODEWITHSUNDEEP
sql-server
Nam Thai
Nam Thai

Reputation: 851

SQL where in clause with array of array

I know that the WHERE ... IN ... clause allows to select all values of a field that exists in a given array. How do I expand this idea to a group of fields? Something like:

SELECT * FROM table_name
WHERE (First, Last) IN ((Adam, Scott), (Betty, Johnson), (Cathy, Wyatt))

Or any other method that allows the same result.

Upvotes: 1

Views: 1796

Answers (3)

Martin Smith
Martin Smith

Reputation: 453232

The syntax in the question is valid in some other RDBMSs but not SQL Server. If on 2008+ you can use

SELECT *
FROM   table_name t
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('Adam', 'Scott'),
                             ('Betty','Johnson'),
                             ('Cathy','Wyatt')
                              ) v(first, last)
              WHERE t.first = v.first AND t.last = f.last

Upvotes: 2

Malk
Malk

Reputation: 11983

It depends on what you are after really. If you just want to combine 2 fields then you can use something like this:

SELECT * FROM table_name
WHERE First+','+Last IN ('Adam, Scott', 'Betty, Johnson', 'Cathy, Wyatt')

Upvotes: 1

PeonProgrammer
PeonProgrammer

Reputation: 1535

SELECT * FROM Table_name WHERE First IN ('Adam', 'Betty', 'Cathy') AND Last IN ('Scott', 'Johnson', 'Wyatt')

Upvotes: -3

Related Questions