|Subject:||OT - SQL Help needed...
|Date:||Fri, 2 Dec 2016 13:46:41 +1100
|From:||Tom Krieg <"tom krieg AT outlook.com">
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.
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
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?
To send me an email, remove the spaces.