Paradox Community

Items in pnews.paradox-client_server

Subject:Re: OT - SQL Help needed...
Date:Fri, 2 Dec 2016 17:44:49 +1100
From:Tom Krieg <"tom krieg AT">
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.
>> 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.

Copyright © 2004