Re: Can't get my query right!!
Date: 1997/08/08
Message-ID: <33eb1c70.87872420_at_resunix.sickkids.on.ca>#1/1
You don't specify what criteria is used to choose a City other than the key. Since the key is not a one-to-one relationship, but a one-to-many (one name and many addresses) your query must specify which city to choose. MAX will choose the highest city name alphabetically, MIN the lowest.
this query will select the MAX city:
SELECT DISTINCT n.name, n.key, a.key, a.address
FROM addresses a, names n
WHERE a.address=(SELECT MAX(a2.address) from adresses a2
WHERE n.key=a.key)
ORDER BY n.name
;
On Wed, 06 Aug 1997 14:29:12 -0400, David Fabi <dfabi_at_crim.ca> wrote:
>Hello,
>
> I'm having some problems in defining my query.
>
>Here's a simplified version of my problem
>
>I have two tables
>
>
>Name Key Key Adresse
>------------- -----------------
>John 1 1 Montreal
>Micheal 2 1 New-York
>Peter 3 1 Berlin
> 2 London
> 2 Paris
> 3 Hong Kong
>
>I wish to match each Name with one of its adresses.
>
>The resulting query should be something like
>
>
>John 1 1 Montreal
>Micheal 2 2 London
>Peter 3 3 Hong Kong
>
>It does look simple, but I don't seem to find the trick...
>
>Thanks
>--
>David Fabi
>Agent Technique, Services Informatique
>Centre de Recherche Informatique de Montréal (CRIM)
>1801, avenue McGill Collège, bureau 800
>Montréal (QC) H3A 2N4,
>Télephone : (514) 840-1235 poste 2387
>Télécopieur : (514) 840-1244
>Internet : dfabi_at_crim.ca
>
>
Received on Fri Aug 08 1997 - 00:00:00 CEST