CODEWITHSUNDEEP
sqlsql-serversql-server-2016
mybirthname
mybirthname

Reputation: 18127

Search for multiple values in IN clause located in where clause

I'm extending search functionality and I need to add search for multiple values in IN clause. So I have textbox and write some number in it. This is dynamic functionality which could add query syntaxis to the WHERE clause of the sql and should be added only when you search.

Let's say we put 1056 in our textbox to search for it.

SELECT
   * 
FROM 
    QueryTable 
WHERE 
   (Select ID from SearchTable Where Number='1056') IN 
         (SELECT value FROM OPENJSON(JsonField,'$.Data.ArrayOfIds'))

This is working properly because searching for ID in SearchTable returns one row, but I want to make it work when returns multiple rows, something like this:

(Select ID from SearchTable Where Number like '%105%')

This obvious gives an error, because returns multiple records. What is the approach to search multiple values(or array of values) in IN clause.

Upvotes: 0

Views: 230

Answers (1)

Thom A
Thom A

Reputation: 95573

Use an EXISTS. I think this is what you need (no sample data to test with to confirm):

FROM QueryTable QT
WHERE EXISTS (SELECT 1
              FROM SearchTable ST
                   JOIN OPENJSON(QT.JsonField,'$.Data.ArrayOfIds') J ON ST.ID = J.[value]
              WHERE ST.Number LIKE '%105%')

Note: I've added aliases to everything, but I've guessed the alias for JsonField. Quantifying your columns is really important to avoid unexpected behaviour and make your code easier to read for both yourself and others.

Upvotes: 1

Related Questions