trying to join 2 tables on zip code where both tables contain first 5 matching zips but other table includes 10 digit zips

Question

Here is what I have. The zip codes in the Customer table may have 5 or 10 digit zip codes where the zip codes in the Counties table contains all of the 5 digit zip codes. I thought the left(c.[Post Code], 5) would truncate the 'on' in the join but doesn't.

 select     c.[No_], 
            c.[Name], 
            c.[Address],  
            c.[City], 
            left(c.[Post Code], 5), 
            kc.[County]
            from [Customer]c
            left join [Counties]kc on c.[Post Code] = kc.[Post Code]

Answer 1

I hate to say this, but you aren't joining on the zip codes. If you were, you could use

where left(c.[Post Code], 5) = kc.whatever-the-post-code-value-is.

In this case, you are going to return the customer information at least once and potentially as many times as there are counties for a city. But the postal code will always be the customer.post code.