|Subject:||Re: OT - SQL Help needed...
|Date:||Fri, 2 Dec 2016 17:44:49 +1100
|From:||Tom Krieg <"tom krieg AT outlook.com">
Liz, looking at your rewrite, I need to point out that there can be many
aliases for the one locality. So left joining the alias is going to
create a problem. That's why I had the IN clause, because the user may
enter one of perhaps 3 aliases for a locality so I want to pick up the
original address record plus the locality that matches the user's entry
(of many aliases for the primary locality). The way I can visualize it
is the IN clause is the only possibility.
On 2/12/2016 2:06 PM, Liz McGuire wrote:
> 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
> 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 =
> WHERE ADD.street_name LIKE 'MAIN%'
> AND (LOC.locality_name = 'FREDS TOWN'
> OR ALS.locality_name = 'FREDS TOWN');
> 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.
>> 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?
To send me an email, remove the spaces.