Re: Query Help

On Dec 21, 4:37 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 21, 4:11 pm, cnbla..._at_juno.com wrote:
> > On Dec 21, 2:06 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > On Dec 21, 12:48 pm, cnbla..._at_juno.com wrote:
>
> > > > I am a beginning Oracle 9i user and I was hoping that someone can help
> > > > me.  I think I have the basic SQL statements down pat, but this
> > > > problem is a little too advanced for me.
>
> > > > Let's say that I have Table A with the following columns:  Food_ID,
> > > > Descr, Cat_ID and Status.  Here are some sample rows in the table:
> > > > 301  Apple  10  Active
> > > > 301  Ham  20 Inactive
> > > > 301  Carrot 30 Active
> > > > 302  Orange  10 Inactive
> > > > 302  Balony  20  Active
>
> > > > I would like to find all instances where the Food_ID does not have a
> > > > Cat_ID of 30 or has a Cat_ID of 30, but it is Inactive. In the example
> > > > shown above, I would see Food_ID of 302, but not 301.
>
> > > > Thank you,
>
> > > If you are famailar with BASIC programming, a similar construct might
> > > look like this:
> > >   IF (AGE <> 65) OR (AGE = 65 AND RETIRED = "Y") THEN
>
> > > The WHERE clause syntax in Oracle is similar to the above, if you omit
> > > the "IF" and the "THEN", and replace the double quotes with single
> > > quotes.  All that you would then need to do is substitute your column
> > > names and the values that you are checking.
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
>
> > From what you have suggested, I would run something like the
> > following:
> > Select Distinct Food_ID
> > From Table A
> > Where (Cat_ID <> 30) OR (Cat_ID = 200 AND Status = 'I')
>
> > If I run the query that you suggest, wouldn't I see Food_ID 302
> > because there is one row for cat_ID 10 and another for 20?  I only
> > want to see a result of 302.  Thank you for your help and patience.
>
> Your translation is correct, however...
> If your data looks like this:
> Descr, Cat_ID, Status
> 301  Apple  10  Active
> 301  Ham  20 Inactive
> 301  Carrot 30 Active
> 302  Orange  10 Inactive
> 302  Balony  20  Active
>
> The portion of the WHERE clause (before the OR) would return the
> following, as it excludes everything where Cat_ID is equal to 30:
> Descr, Cat_ID, Status
> 301  Apple  10  Active
> 301  Ham  20 Inactive
> 302  Orange  10 Inactive
> 302  Balony  20  Active
>
> The second half of your WHERE clause (after the OR) would return no
> rows.
>
> I think that I now understand what you are trying to do: for each
> distinct value of DESCR, if any rows have a CAT_ID of 30 with a STATUS
> of Active, you want to completely exclude that DESCR value from the
> rows returned.  We need a different approach, if this is the case.
> First, we need to define the DESCR values that we want to exclude:
> SELECT DISTINCT
>   DESCR
> FROM
>   TABLEA
> WHERE
>   CAT_ID=30
>   AND STATUS='Active';
>
> The above will return 301, so we want to exclude that record.  There
> are multiple ways to accomplish this:
> SELECT DISTINCT
>   TA.DESCR
> FROM
>   TABLEA TA,
>   (SELECT DISTINCT
>     DESCR
>   FROM
>     TABLEA
>   WHERE
>     CAT_ID=30
>     AND STATUS='Active') TN
> WHERE
>   TA.CAT_ID=TN.CAT_ID(+)
>   AND TN.CAT_ID IS NULL;
>
> SELECT DISTINCT
>   TA.DESCR
> FROM
>   TABLEA TA
> MINUS
> SELECT DISTINCT
>   CAT_ID
> FROM
>   TABLEA
> WHERE
>   CAT_ID=30
>   AND STATUS='Active';
>
> SELECT DISTINCT
>   TA.DESCR
> FROM
>   TABLEA TA
> WHERE
>   TA.DESCR NOT IN (
>     SELECT DISTINCT
>       DESCR
>     FROM
>       TABLEA
>     WHERE
>       CAT_ID=30
>       AND STATUS='Active');
>
> There are often several ways to write a SQL statement to solve a
> particular problem, and some ways are more efficient than others.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Slight correction of the first SQL statement to correct column names in the final WHERE clause:
An inline view:
SELECT DISTINCT
  TA.DESCR
FROM
  TABLEA TA,
  (SELECT DISTINCT
    DESCR
  FROM
    TABLEA
  WHERE
    CAT_ID=30
    AND STATUS='Active') TN
WHERE
  TA.DESCR=TN.DESCR(+)
  AND TN.DESCR IS NULL; Using MINUS:
SELECT DISTINCT
  TA.DESCR
FROM
  TABLEA TA
MINUS
SELECT DISTINCT
  CAT_ID
FROM
  TABLEA
WHERE
  CAT_ID=30
  AND STATUS='Active';

A subquery:
SELECT DISTINCT
  TA.DESCR
FROM
  TABLEA TA
WHERE
  TA.DESCR NOT IN (
    SELECT DISTINCT
      DESCR
    FROM
      TABLEA
    WHERE

      CAT_ID=30
      AND STATUS='Active');

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 21 2007 - 15:42:41 CST