Re: Need hint: messy SQL query problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 08 May 1998 20:00:48 GMT
Message-ID: <3554640d.25546483@192.86.155.100>

A copy of this was sent to "Roger Loeb" <rloeb_at_martech.com> (if that email address didn't require changing) On Fri, 8 May 1998 13:33:22 -0600, you wrote:

>I need some suggestions about how to approach a specific query problem.
>Simplified, I have two tables: A and B. "A" has a non-unique key that is
>indexed. "B" is indexed on the same key and is a subset of "A," i.e.,
>anything in B is also in A at least once.
>
>I need to group A in a certain manner, then select one row from the group
>(using MAX()), but only if none of the rows in the group have a
>corresponding row in table B.
>
>I know there's a straightforward way to do this, and I'm sure I've done it
>before, but I'm blocking on the approach.
>
>Got a hint???
>
>
>Rog

select max(a.x)
  from a, b
 where a.key = b.key (+)
   and b.key is NULL
/

is one way. the outer join from A to B creates a set of all A's that do not have a mate in B since we said and b.key is null.

select max(a.x)
  from a
 where not exists ( select NULL

                      from b
                     where a.key = b.key )

/

is another way.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 08 1998 - 15:00:48 CDT