query not working

[kburrows]

Hi All,

I need to run a query that will return only the inc_num of the rows that have P1. If the inc_num has P1 and P2 then I don't want that inc_num. Here is my query, where is it wrong, it keeps returning all inc_nums even if they have more than P1 and P3, etc.

Select distinct inc_num, ename, lname
From lust, ust, elf, subst
Where elf.inc_num = lust.inc_num
And lust.id = ust.id
And ust.subst_code = subst.subst_code
and ust.subst_code not in
(select ust.subst_code
from ust
where subst_code in ('H','H1','H2', 'P', 'P2', 'P3', 'P4', 'P5', 'UN'))


Thanks in Advance.
Kathy

[slimdave]

This may not be the optimum way, but ...

Select Distinct
   Max(inc_num),
   ename,
   lname
From
   lust,
   ust,
   elf,
   subst
Where
   elf.inc_num = lust.inc_num And
   lust.id = ust.id And
   ust.subst_code = subst.subst_code
Group By
   ename,
   lname
Having
   Count(Distinct inc_num) = 1 And
   Max(inc_num) = 'P1'

... or ...

Select
   inc_num,
   ename,
   lname
From
(
Select Distinct
   inc_num,
   ename,
   lname,
   Count(Distinct inc_num) Over (Partition By ename,lname) inc_nums
From
   lust,
   ust,
   elf,
   subst
Where
   elf.inc_num = lust.inc_num And
   lust.id = ust.id And
   ust.subst_code = subst.subst_code
)
Where
   inc_nums = 1 And
   inc_num = 'P1'

There's other ways as well, of course.

[kburrows]

Thanks for the help,

I couldn't get either of these too work. Keep getting errors when trying to replace table_name, etc.

Can you take another look. Here are the tables. I need to:

Select lust_site.incident_number,
elf_submission.submit_number,
elf_submission.amt_requested,
elf_submission.amt_approved,
substance.stubstance_desc
(Only where the substance code = 'P1', if the specific incident_number exists with other codes besides 'P1', such as 'P2', 'P3' then I want to eliminate that incident_number from the result set.

lust_site
INCIDENT_NUMBER NOT NULL VARCHAR2(9)
UST_FACILITY_ID NUMBER(10)

ust_tank
UST_FACILITY_ID NOT NULL NUMBER(10)
SUBSTANCE_CODE VARCHAR2(2)

elf_submission
INCIDENT_NUMBER NOT NULL VARCHAR2(9)
SUBMIT_NUMBER NOT NULL NUMBER(6)
RECEIVED_DATE DATE
AMT_REQUESTED NUMBER(16,2)
AMT_APPROVED NUMBER(16,2)

substance
SUBSTANCE_CODE NOT NULL VARCHAR2(2)
SUBSTANCE_DESC VARCHAR2(15)
SORT_ORDER NUMBER(6)

[kburrows]

I was wondering. Would a query like this be considered a
easy >> med >> difficult skill level for a developer?

thx,
Kathy