How to find records by comparing two tables in oracle

Question

I have two tables A and B.Both tables are foreingkeyed by Psslno which is primary key in B and foreign key in A.Here what i want is, i need the records from table B where Pid should not suffix with character S and same time all the records coming under table B for Pssslno, is having working status(greater than 100).how to get get this sample given below ,from table B Psslno 1000 fails because one of records in table A is having status 80.Pssslno 1100 satisfies since Pid doesnot suffix S and
all are wokring in table A (>100).Pssslno 1200 fails because Pid 101S suffixes S ,and Psslno 1300 satisfies Table A shown below

Sslno   Sid   tech     status    height     Psslno                    

100     89    G        80        11          1000                      

101 91    U        110        11         1000                      

102     93    L        110        11         1000                      
-------------------------------                     

106     98    G        110        34         1100               

107     99    U        118        34         1100
--------------------------------

109     101   G        110        54         1200

110     102   U        110        54         1200

111     103   L        118        54         1200
--------------------------------

112     105   G        110        54         1300

113     106   U        110        54         1300

114     107   L        118        54         1300

Table B shown below

 Psslno   Pid     Location    Type

 1000      89    AJM         Mic  
 1100      98    SHJ         MAC
 1200      101S   DBB         LAC
 1300      105    ABB         SAC

What query i did is

SELECT a.*
  FROM B a
  INNER JOIN A b
  ON a.Psslno   =b.Psslno   
  WHERE b.status>100
  AND a.Pid NOT LIKE'%S%'

Does that okey??

Answer 1

What you need here is an anti-join. It could be written a bit more simply, with a NOT IN condition, if column psslno was not nullable in table A.

select psslno
from   b
where  pid not like '%S'
  and  not exists ( 
                    select *
                    from   a
                    where  psslno  = b.psslno
                      and  status <= 100
                  )
;

EDIT Based on OP clarification - he needs to retrieve all the information from both tables where psslno is returned by the query above...

One (efficient) way to do this is to use the analytic function MIN() for status, partitioned by psslno:

with
  a ( sslno, sid, tech, status, height, psslno ) as (
    select 100,  89, 'G',  80, 11, 1000 from dual union all
    select 101,  91, 'U', 110, 11, 1000 from dual union all
    select 102,  93, 'L', 110, 11, 1000 from dual union all
    select 106,  98, 'G', 110, 34, 1100 from dual union all
    select 107,  99, 'U', 118, 34, 1100 from dual union all
    select 109, 101, 'G', 110, 54, 1200 from dual union all
    select 110, 102, 'U', 110, 54, 1200 from dual union all
    select 111, 103, 'L', 118, 54, 1200 from dual union all
    select 112, 105, 'G', 110, 54, 1300 from dual union all
    select 113, 106, 'U', 110, 54, 1300 from dual union all
    select 114, 107, 'L', 118, 54, 1300 from dual
  ),
  b ( psslno, pid, location, type ) as (
    select 1000, '89'  , 'AJM', 'Mic' from dual union all  
    select 1100, '98'  , 'SHJ', 'MAC' from dual union all
    select 1200, '101S', 'DBB', 'LAC' from dual union all
    select 1300, '105' , 'ABB', 'SAC' from dual
  )
-- End of input data FOR TESTING ONLY (not part of the solution).
-- SQL query begins BELOW THIS LINE; use your actual table and column names.
select   q.sslno, q.sid, q.tech, q.status, q.height, q.psslno,
         b.pid, b.location, b.type
from     ( select a.*, min(status) over (partition by psslno) as min_status
           from   a
         ) q
         inner join b on q.psslno = b.psslno
where    q.min_status > 100
  and    b.pid not like '%S'
order by q.sslno    -- If needed.
;

Output:

SSLNO SID TECH STATUS HEIGHT PSSLNO PID  LOCATION TYPE
----- --- ---- ------ ------ ------ ---- -------- ----
  106  98 G       110     34   1100 98   SHJ      MAC
  107  99 U       118     34   1100 98   SHJ      MAC
  112 105 G       110     54   1300 105  ABB      SAC
  113 106 U       110     54   1300 105  ABB      SAC
  114 107 L       118     54   1300 105  ABB      SAC