sql help 2

[FALCONSEYE]

I have the following table called tmp_p :

CREATE TABLE xxx.TMP_P
(
  PERSONID  NUMBER,
  NAME      VARCHAR2(20 BYTE),
  ACCID     NUMBER
)
TABLESPACE xxx
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX xxx.TMP_P_PK ON xxx.TMP_P
(PERSONID)
LOGGING
TABLESPACE xxx
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE xxx.TMP_P ADD (
  CONSTRAINT TMP_P_PK
 PRIMARY KEY
 (PERSONID)
    USING INDEX 
    TABLESPACE xxx
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
SET DEFINE OFF;
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (1, 'person1', 1);
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (2, 'person2', 2);
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (3, 'person2', 10);
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (4, 'person3', 5);
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (5, 'person3', 1);
Insert into xxx.TMP_P
   (PERSONID, NAME, ACCID)
 Values
   (6, 'person4', 6);
COMMIT;

1 person1 1
2 person2 2
3 person2 10
4 person3 5
5 person3 1
6 person4 6

I am trying to write a join statement with the following conditions.
a. if a person has an accID 1, exclude him.
b. if a person has an accID 2 and 10, exclude him,

therefore, the sql statement should only return person6. I am having trouble writing a join statement. any help?

thanks in advance.

ColdFusion Ninja for hire.

 

[FALCONSEYE]

select *
from tmp_p p1 where name not in  ( select name from tmp_p where accID = 1 ) 
and name not in  ( select name from tmp_p where accID in (2,10) ) 
order by p1.name

this kind of works but I don't want to use the name field. instead, i want to use the personID field.

ColdFusion Ninja for hire.

 

[SantaMufasa]

FalconsEye,

Perhaps I'm missing something, but can you not achieve your objective with this simpler code?:

select *
  from tmp_p p1 where accid not in (1,2,10)
 order by p1.name;

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[FALCONSEYE]

see person3: he has accID 1 and 5. Your query returns person3 and person4. I need only person4.

thanks

ColdFusion Ninja for hire.

 

[SantaMufasa]

Sorry...I missed the distinction. Try this code instead:

select *
  from tmp_p
 where name not in (select name from tmp_p p1 where accid in (1,2,10))
 order by name;

PERSONID NAME            ACCID
-------- ---------- ----------
       6 person4             6

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Sorry yet again (it must be the end of the day). I forgot to address your request to do the filtering by PERSONID:

select *
  from tmp_p
 where personid
       not in (select personid from tmp_p
                where name
                      in (select name from tmp_p p1 where accid in (1,2,10)
                         )
               )
 order by name;

PERSONID NAME            ACCID
-------- ---------- ----------
       6 person4             6

Let us know if this meets your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

And, BTW, FalconsEye, it is a pleasure to help resolve and issue such as yours when you post the code (as you did) to CREATE and INSERT data into the tables !

Thanks, and hava

for your helpful post that provided the SQL to simplify the solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[FALCONSEYE]

this problem is solved. off to the next one...

ColdFusion Ninja for hire.

 

[maswien]

From what FALCONSEYE described, if we have another record like following in the table temp_p,

7 person7 2

it should be included in the result. But none of the query from Mufasa or FALCONSEYE returns this record. I think the correct answer should like following:

select * from tmp_p
where name not in
( select name from tmp_p
where accID in (2,10)
group by name
having count(distanct accID) > 1)
and accID <> 1