From: owrkrj@mailhub.sickkids.on.ca (Joost Ouwerkerk)
Subject: Re: Can't get my query right!!
Date: 1997/08/08
Message-ID: <33eb1c70.87872420@resunix.sickkids.on.ca>#1/1
References: <33E8C278.13703AF7@crim.ca>
Organization: The Hospital For Sick Children
Newsgroups: comp.databases.oracle.tools



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@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@crim.ca
>
>