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,

On Dec 21, 12:48*pm, cnbla... (AT) juno (DOT) com wrote:
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.

On Dec 21, 2:06 pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:
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.

On Dec 21, 4:11*pm, cnbla... (AT) juno (DOT) com wrote:
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.

On Dec 21, 4:37*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:
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.

In article <ca0b7d13-f686-4233-8700-13316c17c3be (AT) e25g2000prg (DOT) googlegroups.com>, Charles Hooper <hooperc2000 (AT) yahoo (DOT) com> wrote:

There's no point in examining AGE in the second clause.
IF (AGE <> 65) OR (RETIRED = "Y") THEN
has exactly the same effect.

--
Regards,
Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.

In article <ca0b7d13-f686-4233-8700-13316c17c3be (AT) e25g2000prg (DOT) googlegroups.com>, Charles Hooper <hooperc2000 (AT) yahoo (DOT) com> wrote:

There's no point in examining AGE in the second clause.
IF (AGE <> 65) OR (RETIRED = "Y") THEN
has exactly the same effect.

--
Regards,
Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.

In article <ca0b7d13-f686-4233-8700-13316c17c3be (AT) e25g2000prg (DOT) googlegroups.com>, Charles Hooper <hooperc2000 (AT) yahoo (DOT) com> wrote:

There's no point in examining AGE in the second clause.
IF (AGE <> 65) OR (RETIRED = "Y") THEN
has exactly the same effect.

--
Regards,
Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.

In article <ca0b7d13-f686-4233-8700-13316c17c3be (AT) e25g2000prg (DOT) googlegroups.com>, Charles Hooper <hooperc2000 (AT) yahoo (DOT) com> wrote:

There's no point in examining AGE in the second clause.
IF (AGE <> 65) OR (RETIRED = "Y") THEN
has exactly the same effect.

--
Regards,
Doug Miller (alphageek at milmac dot com)

It's time to throw all their damned tea in the harbor again.

On Jan 16, 7:29 pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
You are correct, I probably should have continued the discussion to
simplify the pseudo code. However, my response was intentially
constructed that way to demonstrate to the OP how one might translate
a generic written requirement into basic pseudo code, and from there
into a WHERE clause.

The OP wrote:
"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."

Unfortunately, my pseudo code headed in the wrong direction, as
pointed out in a later post by the OP.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.