The subselect is a big problem... because it will do a big work for server.

On Sun, Jul 22, 2012 at 5:42 PM, Helen Borrie <helebor@...> wrote:

> **
>
>
>
> >Den 2012-07-22 22:35 skrev Ricardo Uzcategui s�h�r:
> >> Maybe if you do a join...
> >>
> >> like this...
> >>
> >> select m.* from master m
> >> join detail d
> >> on (m.id = d.master)
> >> where (d.removed is not null)
> >
> At 09:51 AM 23/07/2012, Kjell Rilbe wrote:
>
> >Thanks for trying, but that would return all master records where there
> >is at least one detail that's not removed. Instead, I need all the
> >masters where there are removed details, but no current ("unremoved")
> ones.
>
> select m.* from master m
> join detail d
> on (m.id = d.master)
> where d.removed is null
> and (not exists
> (select 1 from detail d2
> where d2.master = m.id
> and d2.removed is not null))
>
> I think you said that detail.master is not a declared FK. Do you have
> ascending and descending indexes on it, though?
>
> ./hb
>
>
>



--
Ricardo...


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