Select query in same table name "master" need resultset if status = n, if status = N and status =P the result not display

Question

I have table name master_consmnt with below data

fra_code    mode    cnum    cost    edate       status
ET7867FRA   SURFACE 19001   10.000  2014-01-17  P
ET7867FRA   SURFACE 19005   5.000   2014-01-17  P
ET7867FRA   SURFACE 19005   10.000  2014-01-17  P
FRANE3981   SURFACE 19005   0.000   2014-01-17  P
FRARE5664   SURFACE 19005   18.000  2014-01-17  N
FRARE5664   SURFACE 19001   14.000  2014-01-17  N
FRARE5664   SURFACE 180001  38.000  2014-01-17  N

I want cnum from table master_consmnt where cnum.status=N, please note in master table there is more than one cnum.status data

fra_code    mode    cnum    cost    edate       status
FRARE5664   SURFACE 180001  38.000  2014-01-17  N

The above is the actual result I needed, sorry for the question error posted previously.

Answer 1

As per your Posting title:

... need resultset if status = n, if status = N and status =P the result not display

You may require case sensitive comparison.

select * from mast where binay(status)='n';

This query fetches all records that have status value as lower n.

But, your row data shows none have a n in status but a N or P.

Answer 2

Gordon Linoff a stackoverflow member posted a currect answer to this question which is as follows

select mc.*
from master_consmnt mc
where mc.status = 'N' and
      not exists (select 1
                  from master_consmnt mc2
                  where mc2.cnum = mc.cnum and status = 'P'
                 );