Post Code lookup

[NotSQL]

Can someone help me out or point me in the right direction. I currently have a a table with a list of post codes. I also have a lookup table with a list of post codes, districts and area. Example below:

Table 1

Customer Postcode
123 DE13 OND
256 G2 8JB


Table 2

PostCode District Area
DE Derbyshire Derby
G Strathclyde Glasgow


My problem is that it can either be a 1 character post code or a 2 character post code in the in table 2. How can i find the district or area depending on the postcode?

Cheers

 

[gmmastros]

This won't be the fastest code to execute, but it should work.

Select *
From   Customers
       Inner Join PostCodeLookup
         On PostCodeLookup.PostCode = Left(Customers.PostCode, Len(PostCodeLookup.PostCode))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NotSQL]

unfortunately this is duplicating addresses.

11001108 ML11 0NF M Lancashire
11001108 ML11 0NF ML Lanarkshire

The correct one being the bottom.

 

[earthandfire]

Register for PAF, its all kept in one of the tables provided.


Hope this helps.

 

[gmmastros]

Hmmm.... There's gotta be a better way.

Declare @Customers Table(CustomerId Int, PostCode VarChar(20))

Insert into @Customers Values(123,'DE13 OND')
Insert into @Customers Values(256,'G2 8JB')
Insert into @Customers Values(111,'ML11 0NF')


Declare @PostCodes Table(PostCode VarChar(20), District VarChar(20), Area VarChar(20))

Insert Into @PostCodes Values('DE','Derbyshire' ,'Derby')
Insert Into @PostCodes Values('G' ,'Strathclyde','Glasgow')
Insert Into @PostCodes Values('M' ,'Lancashire','Lancashire')
Insert Into @PostCodes Values('ML' ,'Lanarkshire','Lanarkshire')

Select Customers.*,
       PostCodes.*
From   @Customers Customers
       Inner Join @PostCodes PostCodes
         On PostCodes.PostCode = Left(Customers.PostCode, Len(PostCodes.PostCode))
       Inner Join (
         Select CustomerId,
                Max(PostCodeLength) As MaxLength
         From   (
                Select Customers.CustomerId,
                       PostCodes.PostCode,
                       Len(PostCodes.PostCode) As PostCodeLength
                From   @Customers Customers
                       Inner Join @PostCodes PostCodes
                         On PostCodes.PostCode = Left(Customers.PostCode, Len(PostCodes.PostCode))
                ) As MaxLengths
         Group By CustomerId
         ) As X
         On Customers.CustomerId = X.CustomerId
         And Len(PostCodes.PostCode) = X.MaxLength

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Why not consider adding a POST code abbreviation to the first table and populate it at insert time. Then you can always easliy directly join and not keep doing the same worok over and over each time you need to query both tables.

"NOTHING is more important in a database than integrity." ESquared