Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Query Help! (A Simple Filtering Problem)

Re: Query Help! (A Simple Filtering Problem)

From: David Fitzjarrell <oratune_at_aol.com>
Date: Wed, 10 Jan 2001 17:51:05 GMT
Message-ID: <93i7e5$56i$1@nnrp1.deja.com>

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

group by i.id1;
--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/
Received on Wed Jan 10 2001 - 11:51:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US