CODEWITHSUNDEEP
sqloracle-databaseplsql
Kate
Kate

Reputation: 455

How do I select value on the basis of another value?

I need to look for list of distinct acct_number that has no '0' as Status_code . I have following records in my table:

Acct_number   Status_code 
 1234              0
 1234              1
 1234             -1
 2345              2
 2345              3
 2345             -1
 3456              3
 3456              0
 3456              -1

I want out put be like this:

 acct_number
  2345

Both 1234 and 3456 has '0' as status_code . 2345 is the only one that does not have '0' as status_code

My query looks like this but it does not give right result:

 with cte as(
 select distinct acct_number,count(distinct status_code)  
 from  XXX
 where ---------
 and status_code<>'0'
 group by acct_number
 having count(distinct status_code) >1)
      select  distinct sk.acct_number, a.acct_number, sk.status_code 
      from  XXX b, cte a
      where -----
      and  b.status_code<>'0'
      and b.acct_number=a.acct_number;

Upvotes: 0

Views: 76

Answers (4)

Sony Thomas
Sony Thomas

Reputation: 31

You can use not exists clause to eliminate all accounts which have at least one record with status code=0.

select distinct X1.acct_number  
     from  XXX as X1
     where not exists (select 1 from XXX as X2
     where X2.status_code=0
    and x2.acct_number=x1.acct_number)

Upvotes: 0

Mani Arasaan
Mani Arasaan

Reputation: 11

Just distinct is enough

select distinct Acct_number from table_name where Status_code <> '0';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would use aggregation as:

select Acct_number
from t
group by Acct_number
having sum(case when status_code = 0 then 1 else 0 end) = 0;

Or, if you have a separate table of accounts, then:

select a.*
from accounts a
where not exists (select 1
                  from account_status acs
                  where acs.acct_number = a.acct_number and acs.status_code = 0
                 );

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use not exists

select t1.* from XXX t1
 where not exists ( select 1 from 
                   XXX t2 where t2.Acct_number=t1.Acct_number
                    and Status_code=0)

or you can use aggregation

select Acct_number
from XXX
group by Acct_number
having sum(case when Status_code = 0 then 1 else 0 end)<>1

Upvotes: 0

Related Questions