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

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL syntax help

Re: SQL syntax help

From: Ed prochak <ed.prochak_at_alltel.com>
Date: 1 Feb 2002 06:48:36 -0800
Message-ID: <e51b160.0202010648.5e1bac19@posting.google.com>


You want the MINUS operation

SELECT * from parents
  MINUS
SELECT * from parents where child has condition;

This will do what you said. (whether that's rally what you want is yet another question).

   Ed Prochak

jniels_at_att.net (JohnN) wrote in message news:<61e64477.0201301027.5b28e6f5_at_posting.google.com>...
> I need to construct a query (Oracle 8i)
>
> Each parent record may have many child records.
>
> I want to find parents for which the children do _not_ have a
> particular conditional.
>
> One complication is that I can't simply filter on "not <condition>"
> since almost every parent I want to find will probably have some of
> the conditional children.
> [snip]
>
> ***NOW THE QUESTION***
> I assume the following approach will work:
>
> Link Parent and Child tables
>
> 1. Fetch all Parent records [there are other filters]
>
> 2. Fetch all Parent records with children that DO contain the
> condition I want to filter (this is a subset of the first)
>
> 3. Display the non-intersection of the two parent record sets -- so
> that only records that DO NOT contain the condition I want to filter
> remain.
>
> What SQL syntax will accomplish this? It's not a traditional self-join
> <??>
>
> TIA,
> John
Received on Fri Feb 01 2002 - 08:48:36 CST

Original text of this message

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