SQL - help for select statement

[d4wong]

Example:

table_O
pfield = 667 cfield = 008 stat=C
pfield = 667 cfield = 307 stat=S
pfield = 777 cfield = 222 stat=S
pfield = 888 cfield = 111 stat=S

table_q
pfield = 667 cfield = 008
pfield = 777 cfield = 222

-- Step 1
-- I want all the pfield in table_O (w/ stat=S) that are NOT in table_q
select distinct (o.pfield) from table_O o, table_q q
where o.pfield = q.pfield (+) and q.pfield is null and o.stat = 'S'

Result:
pfield = 888

-- Step 2
-- I want the records from Step 1 and
-- include table_O (w/ stat=S) records that have a matching pfield in table_q, but not a matching cfield
SELECT o.pfield, o.cfield
FROM table_O o WHERE o.stat = 'S' and o.cfield not in (select distinct q.cfield from table_q q where q.pfield = o.pfield)

Result:
pfield = 667 cfield = 307
pfield = 888 cfield = 111

-- Step 3
-- I want the records in table_O (w/ stat=S) that have a matching pfield in table_q, but not a matching cfield

Desired result:
pfield = 667 cfield = 307



Question: Is there a single SQL query (without using a MINUS on the first 2 steps) that can give me the desired result in Step 3?

 

[Dagon]

I'm very confused on what the point of the first two steps is since your final result just seems to come from Step 3. I can't see how the rows from Step 1 will ever be relevant since the condition at Step 3 (that have a matching pfield in table_q) would seem to eliminate them.

The result of Step 1 and Step 2 looks like a UNION, but Step 3 just seems to be a query on its own that's unrelated to the other two.

 

[d4wong]

Hi Dagon,

If the first 2 steps are misleading you, please ignore them. They were just to show that a MINUS could provide the results I needed, but it isn't how I need the query implemented. Thx for looking.

 

[SantaMufasa]

d4wong,

There a multiple methods to obtain the results from Step 3...here is one method. First is are the sample data:

SQL> select * from table_o;

    PFIELD     CFIELD S
---------- ---------- -
       667          8 C
       667        307 S
       777        222 S
       888        111 S
SQL> select * from table_q;

    PFIELD     CFIELD
---------- ----------
       667          8
       777        222

Next is code to produce your results:

select o.pfield,o.cfield
  from table_o o
      ,table_q q
 where not exists (select 'd4wong'
                     from table_q x
                    where o.pfield = x.pfield
                      and o.cfield = x.cfield)
   and o.stat = 'S'
   and o.pfield = q.pfield
/

    PFIELD     CFIELD
---------- ----------
       667        307

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[d4wong]

thanks for helping out a newbie!