CODEWITHSUNDEEP
sqlsql-servert-sql
Akhlaque Khan
Akhlaque Khan

Reputation: 11

SQL Query to get records having specific type only

I have following type of records

account     amount  code        type        c_date
AAAA        100.00  111         C           2018-01-01
AAAA        200.00  666         C           2018-01-01
AAAA        300.00  777         C           2018-01-01 
BBBB        500.00  111         C           2018-01-01 
BBBB        400.00  222         B           2018-01-01 
BBBB        600.00  111         C           2018-01-01 
DDDD        100.00  111         C           2018-01-01 
DDDD        200.00  777         C           2018-01-01 
EEEE        900.00  333         B           2018-01-01 
EEEE        1000.00 222         B           2018-01-01 
EEEE        1200.00 111         C           2018-01-01
FFFF        123.00  111         C           2018-01-01 
GGGG        1223.00 222         B           2018-01-01

I need to retrieve only those accounts which have exclusively only on type = 'C' DESIRED RECORDS ARE:

AAAA 
DDDD
FFFF

My query :

SELECT DISTINCT ACCOUNT,TYPE,COUNT(*) FROM Table_1
WHERE ACCOUNT IN (SELECT account
  FROM Table_1
  group by account
)  
GROUP BY ACCOUNT,TYPE
HAVING TYPE = 'C' 
ORDER BY 1

Upvotes: 1

Views: 540

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272236

You can use NOT EXISTS:

SELECT * -- or DISTINCT Account
FROM t
WHERE NOT EXISTS (
    SELECT 1
    FROM t AS x
    WHERE account = t.account
    AND type <> 'C'
)

Upvotes: 3

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try this

SELECT Distinct account
FROM Table_1
Where type = 'C'
AND Account NOT IN (Select Account from Table_1 Where type <> 'C')

You can check live demo Here.

Upvotes: 1

Related Questions