|Subject:||Re: OT - SQL Help needed...
|Date:||Sat, 3 Dec 2016 12:22:56 +1100
|From:||Tom Krieg <"tom krieg AT outlook.com">
Jim, in Australia, a locality can have more than one postcode and more
than one name. In any case, it's not my database design, it's from the
Australian Government Geocode team (GNAF). I have flattened some of it
because some address lookup queries took minutes to run so I had to add
things like street name into the address details table so I could have a
secondary index on street name lookup. The address details table
contains over 14 million records.
On 3/12/2016 12:57 AM, Jim Moseley wrote:
> My sql is rusty, so I'll let Liz & Larry talk about that. My concern is
> with your design.
> It seems like your Alias table has columns other than just an alias key
> pointing back to the Locality table, like Alias Post Code. This doesn't
> seem correct - Locality should be the only place for those fields. If a
> user wants to specify their own Alias Locality, have them add 2 rows, 1
> to Locality and one to Alias.
> This would then solve your 'where can I get the Post Code from' issue.
>> If the user entered an alias locality and the locality match is on the
>> alias table (IN clause), how can I pick up other fields, such as postal
>> code which may be different to the main locality postal code, in the
>> alias table and put them in the query result? I.e. how do I know which
>> alias record was matched in the IN clause?
> Jim Moseley
To send me an email, remove the spaces.