That seems to make sense.  (It's always hard when you don't have the 
tables and data to explore - at least for me.)

But meanwhile... Now in addition to suffering balaclava weather here, I 
want some baklava, and Greece & Turkey are far, far away...  Thanks 
bunches. :)

Liz


On 02 Dec 2016 20:17, Tom Krieg wrote:
> 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.
>