How do I create a query that uses the value from another table as a WHERE LIKE clause?
For example I have a POSTCODE table consisting of postcode values such as 'DD', 'L', 'WN', etc... But how can I run a query on another table, say CUSTOMERS, by pulling out like values from the postcode one?
If it was hard coded it would read something like:
select * from CUSTOMERS where CUSTOMERS.POSTCODE like 'DD%' or CUSTOMERS.POSTCODE like 'L%' or CUSTOMERS.POSTCODE like 'WN%'
So far I have created a cursor however I am unsure how to get the like postcode values restrict on LIKE.
Answer by Benjamin ·
You could do something like this.
SELECT * FROM CUSTOMERS WHERE CUSTOMERS.POSTCODE IN (SELECT POSTCODE.CODE FROM POSTCODE)
If you have duplicates in the POSTCODE table, you could add the distinct keyword.
SELECT * FROM CUSTOMERS WHERE CUSTOMERS.POSTCODE IN (SELECT DISTINCT POSTCODE.CODE FROM POSTCODE)
Also you can put a WHERE clause on your sub-select like this. But instead of using a hard-coded value, you put a variable. This could alternatively be defined as a stored procedure.
DECLARE @Code varchar(5) SET @Code = 'DD' SELECT * FROM CUSTOMERS WHERE CUSTOMERS.POSTCODE IN (SELECT POSTCODE.CODE FROM POSTCODE WHERE POSTCODE.CODE LIKE @Code + '%')