Just wondering what's the best way for the following problem:
Let's suppose there are 2 tables (Events and EventAddresses) where
EventAddress contains all addresses to Events .

Now a query is needed which only shows all events to a given address.
The problem is that each address can join one event multiple times.

Let's further suppose the following query returns the given results:
SELECT Events.ID, Events.EventDescription, EventAddresses.AddressID,
EventAddresses.FName, EventAddresses.LName
FROM Events
JOIN EventAddresses
ON EventAddresses.EventID = Events.ID

ID EventDescription AddressID FName LName
1 Event 1 1 Smith Joe
1 Event 1 1 Smith Joe
2 Event 2 2 Beck Ramon
3 Event 3 3 Elson Max
3 Event 3 3 Elson Max
4 Event 4 4 Trump Betty
5 Event 5 1 Smith Joe

To get only the events to a given address i would add
"WHERE EventAddresses.AddressID = 1" to the given query
and then i get this result:
ID EventDescription AddressID FName LName
1 Event 1 1 Smith Joe
1 Event 1 1 Smith Joe
5 Event 5 1 Smith Joe

But the following result is needed (prevent multiple event-records for the
same event):
ID EventDescription AddressID FName LName
1 Event 1 1 Smith Joe
5 Event 5 1 Smith Joe

What is the best (fastest) way to achieve this ?
That's what me came into mind so far:
1.) SELECT DISTINCT Events.ID, Events.EventDescription,
EventAddresses.AddressID, EventAddresses.FName,
EventAddresses.LName
FROM Events
JOIN EventAddresses
ON EventAddresses.EventID = Events.ID
WHERE EventAddresses.AddressID = 1
--> problem: not very fast because there are many more needed columns
as used in this example

2.) SELECT Events.ID, Events.EventDescription
FROM Events
WHERE Events.ID IN
(SELECT DISTINCT Events.ID
FROM Events
JOIN EventAddresses.EventID = Events.ID
WHERE EventAddresses.AddressID = 1)
--> problem: not very handy, isn't it ?

3.) A function like in PostgreSQL (DISTINCT ON [Column, Column, ...]):
SELECT DISTINCT ON [Events.ID], Events.EventDescription,
EventAddresses.AddressID, EventAddresses.FName,
EventAddresses.LName
FROM Events
JOIN EventAddresses
ON EventAddresses.EventID = Events.ID
WHERE EventAddresses.AddressID = 1
--> problem: is there anything similarly in ASA ?

Hope anyone does know what i mean ;-) ...

Thanks in advance !

Jens

A simpler version of your 2.) is the solution:

SELECT Events.ID, Events.EventDescription
FROM Events as E1
WHERE exists (SELECT 1
FROM EventAddresses
WHERE EventAddresses.EventID = E1.ID
AND EventAddresses.AddressID = 1)


--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering

"JN" <pjtaker (AT) web (DOT) de> wrote

Thanks for your help Ani !!!
That's exactly what i'm looking for ...

Jens

"Ani Nica" <anica (AT) sybase (DOT) com> schrieb im Newsbeitrag
news:444de4a4$1 (AT) forums-2-dub (DOT) ..