Subject: | Re: OT - SQL Help needed...
| Date: | Fri, 2 Dec 2016 17:44:49 +1100
| From: | Tom Krieg <"tom krieg AT outlook.com">
| Newsgroups: | pnews.paradox-client_server
|
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
> ,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
--
To send me an email, remove the spaces.
|