How To Write A Query To Work With A NOT LIKE Subquery?
How can I write the following query to work with a NOT LIKE subquery?
SELECT custno FROM customers WHERE postcode not like (SELECT postcode FROM mail_postcodes)
The MAIL_POSTCODES table contains values like 'L','BD','WF' however the values in this table are prone to change so the query CANNOT be hardcoded like:
SELECT custno FROM customers WHERE postcode not like 'L%' and postcode not like 'BD%' and postcode not like 'WF%'
So how is it possible to write a NOT LIKE subquery with values pulled from that table?
Thanks in advance.
Actually it is possible. This is how I got round it by creating a cursor which inserted into a temporary table if it matched a like. I then compared the temporary table with the actual one to get the unmatched postcodes:
DECLARE @postcode VARCHAR(10)
DROP TABLE CUSTOMERS_TEMP
SELECT * INTO CUSTOMERS_TEMP FROM CUSTOMERS WHERE 1=2
DECLARE cur_1 CURSOR FAST_FORWARD FOR
SELECT postcode FROM mail_postcodes
OPEN cur_1
FETCH NEXT FROM cur_1
INTO @postcode
WHILE @@FETCH__STATUS = 0
BEGIN
PRINT @postcode
INSERT INTO CUSTOMERS_TEMP
SELECT * FROM CUSTOMERS
WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
LEFT(postcode,2),1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''') LIKE @postcode
FETCH NEXT FROM cur_1
INTO @postcode
END
CLOSE cur_1
DEALLOCATE cur_1
SELECT * FROM CUSTOMERS
WHERE custno NOT IN (SELECT custno FROM CUSTOMERS)
There is probably a quicker easier way but this was my solution.
You can't, but you can do exact matches and disregard these records. one of these may help.
SELECT custno
FROM customers
WHERE postcode not in
(SELECT postcode FROM mail_postcodes)
SELECT custno
FROM customers
WHERE not exists
(SELECT postcode FROM mail_postcodes)
Otherwise you need to define your search criteria a little better, is it the first char, the first two etc? Or have another table with the values you want to disregard, then use dynamic sql to build your query.
Have you tried this?
SELECT * FROM CUSTOMERS as c left join mail_postcodes as pc on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( LEFT(c.postcode,2),1,''''),2,''''),3,''''),4,''''),5,''''),6,''''),7,''''),8,''''),9,''''),0,'''') LIKE pc.postcode+'%'
The problem is that your customers table should store postcode.postcode as a separate column and store the suffix in a different column.
try this:
select c.custno
from customers c
left outer join postcode p
on c.postcode like p.postcode + '%'
where p.postcode is null
