user2961127 user2961127 - 5 months ago 8
SQL Question

Get distinct records from table

I have data table giving following results:

number code
1 A
1 B
1 A
2 B
2 A
3 A
4 B


How do i find distinct number from table having code as A. The number should only have code A in table and not have B in table.

For the above table: My answer would be 3. As it has only code A.

Answer
SELECT DISTINCT a.number 
FROM TableName a
WHERE a.Code = 'A'
AND NOT EXISTS (Select 1 
                FROM TableName b 
                WHERE a.number = b.number 
                 AND  b.Code = 'B')