Re: Query Help

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. Received on Fri Dec 21 2007 - 15:37:43 CST