--- 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