Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Help! (A Simple Filtering Problem)
In our last gripping episode contrapositive <contrapositive_at_mydeja.
com> wrote:
> Hi. I need to know how I can query for rows based on the existence of
> other rows. I've simplified the problem down to this. Suppose I have a
> table with fields called ID1 and ID2, which together make up the key.
> ID1 can be any number and ID2 can be 0, 1 or 2. I want all rows with
> ID2 = 1. If there is no record with ID2 = 1, but there is a
> corresponding record with ID2 = 0, then return it instead. Otherwise
> ignore that record. I know that sounds complicated, but the idea is
> pretty simple. If my table looks like this,
>
> ID1 ID2
> ------ ------
> 581 0
> 581 1
> 581 2
> 582 0
> 582 2
> 583 2
> 584 0
> 584 1
>
> then my query should return
>
> ID1 ID2
> ------ ------
> 581 1
> 582 0
> 584 1
>
> Only one record of ID1 should be returned and its ID2 should be 1, or
0
> if no record with ID2 = 1 exists.
>
> I can't use PL/SQL and I'm trying to avoid a subquery since the actual
> queries I'm working with are already huge. That's what makes this
> difficult. Does anyone see a way to do this using pure SQL? I would
> sincerely appreciate any help at all.
>
> Thanks in advance.
>
> -jk
>
> Sent via Deja.com
> http://www.deja.com/
>
Try this, it should return the result set you specify:
select i.id1, max(i.id2)
from (select id1, id2
from qtest where id2 = 0 union select id1, id2 from qtest where id2 = 1) i
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/Received on Wed Jan 10 2001 - 11:51:05 CST