Are you saying you want matches where either LOC.locality_name or 
ALS.locality_name is 'FREDS TOWN'?  If so (sorry for reformatting, 
needed for me to make sense of it):

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
FROM address_detail ADD
      LEFT JOIN locality LOC ON ADD.locality_uid = LOC.locality_uid
      LEFT JOIN locality_alias ALS ON ALS.primary_locality_uid = 
LOC.locality_uid
WHERE ADD.street_name LIKE 'MAIN%'
AND (LOC.locality_name = 'FREDS TOWN'
OR   ALS.locality_name = 'FREDS TOWN');

?

Liz


On 01 Dec 2016 19:46, Tom Krieg wrote:
> I have 3 tables, a table of addresses (street addresses), linked to a
> table of localities and a third table of locality aliases, also linked
> to the table of localities. Each locality may or may not have an alias,
> so 'My town' could be the primary name of a locality and 'Your town' and
> 'Freds town' could be aliases by which My town could also be known. So
> 'My town' is a row in the locality table, and 'Your town' and 'Freds
> town' are rows in the locality_alias table.
>
> So I want to get an address based on street name and locality, and I
> want to pick up the primary locality name even if the user entered an
> alias name. So I want to pick up 'My town' even if the user entered
> 'Freds town'. I have the following SQL to do that.
>
> 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
> FROM address_detail ADD LEFT JOIN locality LOC
>      ON ADD.locality_uid = LOC.locality_uid
> WHERE ADD.street_name LIKE 'MAIN%'
> AND   (  (LOC.locality_name = 'FREDS TOWN') OR
>          ('FREDS TOWN' IN
>            (SELECT ALS.locality_name
>             FROM locality_alias ALS
>             WHERE ALS.primary_locality_uid = LOC.locality_uid)
>          )
>       );
>
> This works quite well and is fast because I have lots of indexes. My
> problem is ...
>
> 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?
>
> TIA