Posted by cheesey_toastie on 03/23/06 14:09

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

• Next in forum: Connecting to another SQL Server
• Prev in forum: Re: How do I change the default collation string of an installation
• Thread view: Join returns more than one row, Post code regular expressions

[Reply to this message]