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
-- 
To send me an email, remove the spaces.