Posted by cheesey_toastie on 03/23/06 15:54

Hi Damien,

I was first initially dubious that it would work where the post code
matched three different rows e.g.

If postal_code = MK111 1AA ....
MK%
MK1%
MK11%

But it does, and a quick read of
http://www.techonthenet.com/sql/exists.php explained it.
Thanks for that!

Below is the code with the minor corrections of the field names for
anyone following the post...
SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,
rc.POST_CODE, dn.POSTAL_CODE
FROM REGIONAL_CODES rc CROSS JOIN
DEALER_NAW dn
WHERE (dn.POSTAL_CODE LIKE rc.POST_CODE) AND (NOT EXISTS
(SELECT *
FROM REGIONAL_CODES rc2
WHERE dn.POSTAL_CODE LIKE
rc2.POST_CODE AND LEN(rc2.POST_CODE) > LEN(rc.POST_CODE)))

ct

[Back to original message]