Subject: | Re: OT - SQL Help needed...
| Date: | Sat, 3 Dec 2016 14:17:28 +1100
| From: | Tom Krieg <"tom krieg AT outlook.com">
| Newsgroups: | pnews.paradox-client_server
|
Larry & Liz, thanks for your help, it's much appreciated. I made some
changes to your query and came up with the answer. Some background.
A locality can be known by, say, 3 names. One is the Govt 'approved' or
official name, the others can be historic or common usage and the post
office delivers to all of them, and they can have different postcodes to
the official one. So when a user enters an address to look up (to see if
it exists in the geocoded database and is thus a "real" address) I need
to check to see if the user's entered the 'official' name, or one of the
'alias' names. I need to retrieve the details for the 'official'
locality because that's the only one that's geocoded and I do lots of
stuff that depends on latitude and longitude, including displaying a
google map in my app, but I also need the details for the unofficial one
if that's the one the user entered. If the user entered the 'official'
name, we don't need to know anything about any aliases that may exist
because the user's not going to freak out because the locality name is
different to the one they entered..
Here's what I came up with: (names are different to my example because
they come from the real database)
SELECT ADD.address_detail_uid
,ADD.lot_number
,ADD.level_number
,ADD.level_number_suffix
,ADD.flat_number,
,ADD.number_first
,ADD.number_first_suffix
,ADD.street_name
,ADD.street_type
,LOC.locality_uid
,LOC.locality_name
,LOC.state_abbrev
,ADD.address_postcode
,ALS.locality_name AS locality_alias
,ALS.locality_alias_uid
FROM gnaf.adb_address_detail ADD
LEFT JOIN gnaf.adb_locality LOC
ON ADD.locality_uid = LOC.locality_uid
LEFT JOIN gnaf.adb_locality_alias ALS
ON ALS.locality_uid = LOC.locality_uid
AND ALS.locality_name = 'BALACLAVA'
WHERE ADD.street_name = 'INKERMAN'
AND ADD.number_first = 33
AND ADD.street_type LIKE 'ST%'
AND (ALS.locality_name = 'BALACLAVA' OR
LOC.locality_name = 'BALACLAVA');
Thanks again for helping me to figure this out.
On 3/12/2016 8:28 AM, Larry DiGiovanni wrote:
> SELECT ADD.address_uid
> ,ADD.unit_number
> ,ADD.street_number
> ,ADD.street_suffix
> ,ADD.street_name
> ,LOC.locality_uid
> ,LOC.locality_name
> ,LOC.state
> ,LOC.postcode
> ,ALS.locality_name alias_locality_name
> ,ALS.state alias_state
> ,ALS.postcode alias_postcode
> FROM address_detail ADD
> LEFT JOIN locality LOC ON ADD.locality_uid = LOC.locality_uid AND
> LOC.locality_name = 'FREDS TOWN'
> LEFT JOIN locality_alias ALS ON ALS.primary_locality_uid =
> LOC.locality_uid AND ALS.locality_name = 'FREDS TOWN'
> WHERE ADD.street_name LIKE 'MAIN%
> AND (ALS.state IS NOT NULL OR LOC.state IS NOT NULL);
--
To send me an email, remove the spaces.
|