CODEWITHSUNDEEP
sqloracle-database
Vishnu Priyan
Vishnu Priyan

Reputation: 35

Selecting records based on different conditions on a column

Below is sample records in 2 tables

Person Table

Per_Id      +     Per_type   
--------------------------------------------
000001            SINGULAR
000002            COLLECTIVE
000003            INSTITUTION
000004            INSTITUTION
000005            SINGULAR

Person Char Table

Per_Id      +     Char_Type        +    Char_Val
-------------------------------------------------------------------------------
000002            STAT_TY               SUSPENSION
000002            STAT                  COMPLETE

000003            STAT_TY               SUSPENSION
000003            STAT                  COMPLETE

000004            STAT_TY               CESSATION
000004            STAT                  COMPLETE

000005            STAT_TY               DEATH
000005            STAT                  PENDING

Requirement is that I need to select the persons with following conditions

  1. The person that do not have any entry in the Person Char Table (Working fine)
  2. Person that has entry in the char table but the value of char STAT is not COMPLETE (Working fine)
  3. The person of type INSTITUTION that has char with value SUSPENSION

In the above table person IDs returned from the query are

000001 (No record in char table)
000005 (The value of STAT char is not COMPLETE)
000003 (Institution person and char value is SUSPENSION)

Below is the query in which I need help in the clause for selecting records that satisfies point 3.

SELECT P.PER_ID,
C.CHAR_VAL, C1.CHAR_VAL
FROM PERSON_TBL P
LEFT OUTER JOIN CHAR_TBL C ON C.PER_ID = P.PER_ID AND C.CHAR_TYPE = 'STAT_TY'
LEFT OUTER JOIN CHAR_TBL C1 ON C1.PER_ID = P.PER_ID AND C1.CHAR_TYPE = 'STAT'
WHERE
1=1
AND NVL(C.CHAR_VAL,' ')  NOT IN ('CESSATION','DEATH')
AND NVL(STCS.SRCH_CHAR_VAL,' ')  NOT IN ('COMPLETE')
AND ***need clause for selecting person of type SINGULAR and with char SUSPENSION***

Upvotes: 1

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

How about exists and not exists:

select p.*
from person_tbl p
where not exists (select 1
                  from person_char pc
                  where pc.per_id = p.per_id
                 ) or
      exists (select 1
              from person_char pc
              where pc.per_id = p.per_id and
                    pc.char_type = 'STAT' and
                    pc.char_value <> 'COMPLETE'
            ) or
      (p.per_type = 'INSTITUTION' and
       exists (select 1
               from person_char pc
               where pc.per_id = p.per_id and
                     pc.char_type = 'STAT_TY' and
                     pc.char_value = 'SUSPENSION'
             )
      );

Upvotes: 1

Related Questions