Paradox Community

Items in pnews.paradox-client_server

Subject:Re: OT - SQL Help needed...
Date:Sat, 3 Dec 2016 14:17:28 +1100
From:Tom Krieg <"tom krieg AT">
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
       ,ALS.locality_name AS locality_alias
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%

To send me an email, remove the spaces.

Copyright © 2004