The like function is quite handy to use in SQL; however, it cannot be used in an “in” statement. The “in” statement is effectively using “OR =”, and there is no equivalent that uses and “OR LIKE”.
For example, lets say you have a list of names, and you want to see all last names that start with vowels. You can write it like this:
SELECT LastName FROM NameList
WHERE LastName LIKE ‘a%’ OR LastName LIKE ‘e%’ OR LastName LIKE ‘i%’ or LastName LIKE ‘o%’ or LastName LIKE ‘u%’
This would make it much easier to read, but it doesn’t work.
SELECT LastName FROM NameList
WHERE LastName IN (‘a%’,’e%’,’i%’,’o%’,’u%’)
You can workaround this limitation by using an inner join of select with the union statement
SELECT n.LastName
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch
This makes your filter a column, and if needed you can add it to your select statement
SELECT f.FilterMatch, n.LastName
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch
Lastly, if you need to join this to more tables, just add more joins.
SELECT f.FilterMatch, n.LastName, n.CustID, o.ordernumber, o.orderdate, o.ordertotal
FROM NameList AS n
INNER JOIN
(
SELECT 'a%' FilterMatch
UNION SELECT 'e%'
UNION SELECT 'i%'
UNION SELECT 'o%'
UNION SELECT 'u%'
) f ON n.LastName LIKE f.FilterMatch
INNER JOIN
Orders AS o ON n.CustID = o.CustID