By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Join returns more than one row, Post code regular expressions

P: n/a
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

Mar 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Mar 23 '06 #2

P: n/a
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

Mar 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.