----- Original Message -----
From: dianeb77x
To: ib-support@yahoogroups.com
Sent: Wednesday, December 05, 2001 10:24 PM
Subject: [ib-support] Re: Select distinct record from the Master table through a join


--- In ib-support@y..., "Junior Ang" <junior@c...> wrote:
> Hi,
>
> Greetings.

Likewise!

>
> If I want to retrieve records from the master table which should not
duplicate, when the criteria are to be based on the detail table
records, is this the best and only way?
>
> select distinct A.ID from TABLE1 A inner join TABLE2 B on A.ID =
B.ID where B.QTY > 100
>
> where TABLE1 is the master table and TABLE2 is the detail table.
>

You could also try something like this:

select A.ID from TABLE1 A where exists (select * from TABLE2 B where
B.ID = A.ID and B.QTY > 100)

Doesn't use a join (therefore eliminating need to use 'distinct',
assuming A.ID is unique in TABLE1), just checks for presence or
absence of detail records matching your criteria.

Not sure if it's better or worse, just different.

Cheers,
db



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/





[Non-text portions of this message have been removed]