Posted by Damien on 03/23/06 15:28

cheesey_toastie wrote:
> Hi,
>
> I trying to write a select statement that will return each of my sales
> men a region code based on a table of post codes using wildcards... eg.
> MK1 1AA would be matched in the region code table to MK1%
>
> 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)
>
> The above statement works BUT there are some post code areas such as
> our friends in Milton Keynes that are split into two regions... eg MK1
> is region id 2 and MK10 is region 3.
>
> So a dealer with post code MK10 1AA would be matched to both rows
> returning duplicates
> POST_CODE REGION_ID
> MK1% 2
> MK10% 3
>
> I think the answer would lie in a subquery which returns the ID of the
> region with the longest length of the postcode match (e.g.
> len(POST_CODE) for the rc table... return only the MAX....
>
> any ideas????
>
> Any help muchos appreciated, and I apologies now for the naming of the
> dealers name as a reserve word... not me!
>
> Ct

Hi Ct,

I think it would be along the lines of:

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.POSTAL_CODE) > LEN(rc.POSTAL_CODE))

Damien

[Back to original message]