GoatInTheMachine GoatInTheMachine - 2 months ago 9
SQL Question

SQL query over multiple inputs

I've got table containing a number of text columns, and need to be able to query the table using a search term. For single terms, this is simple...

SELECT * FROM things WHERE @searchTerm in (column1, column2...columnX)


However, sometimes I need to search over multiple terms in a single query, something like...

SELECT * FROM things WHERE @searchTerms in (column1, column2...columnX)


...where
@searchTerms
is a collection of values, rather than a single value. Is there a way to emulate this behaviour in SQL?

UPDATE:
Thank you @Tedo G., unfortunately I need the query to use
AND
between the different terms. To do that I've added a change to yours to group the results and ensure the count per distinct result matches the number of terms...

SELECT
*
FROM
things AS A INNER JOIN @searchTerms AS B
ON B.[FieldName] IN (column1, column2...columnX)
GROUP BY column1, column2...columnX
HAVING count(column1) = @numberOfSearchTerms


...this works, but is there a better way?

Answer

One other way to achieve this is to make a table variable and fill it with your search term values, then join it :

SELECT 
     * 
FROM 
     things AS A INNER JOIN @searchTerms AS B
          ON B.[FieldName] IN (column1, column2...columnX)