Ok, after much pondering I have come up with the following...
SELECT od.orderid, od.postcode, fs.postcodearea, fs.flatsortcode
FROM amazonemailorderdetails od
JOIN flatsortareas fs
ON (left(od.PostCode,CASE PostCode REGEXP '^[a-z|A-Z][0-9]' when 1 then
1 else 2 END) = fs.postcodearea)
This is not completely acurate as it currently only copes with 1 or 2 letter
areas which would fall over on BFPO, however there is currently not a BF
postcode area so by shortening BFPO in the flatsortareas table to just BF I
will still get a positive match.
Lets hope for improved regex support in the future...
--
Jay
> -----Original Message-----
> From: James Smith [mailto:[EMAIL PROTECTED]
> Sent: 28 June 2006 09:42
> To: CF-Community
> Subject: RE: Unusual table join
>
> There was a rather ingenious solution posted to a web forum,
> but unfortunately it didn't work as expected...
>
> ON LEFT(a.postcode, LENGTH(b.code)) = b.code
>
> Very simple solution I wouldn't have thought of in a thousand
> years, but it doesn't work...
>
> If the postcode in question was (for example) BD10 0xx it
> would actually get matched to "B" in the code table and not
> "BD". Nice idea though.
>
> --
> Jay
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.9.5/377 - Release
> Date: 27/06/2006
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:5:210398
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
