how can i find which attribute value only contain one type of value?

Question

My question may not be clear, but basically I have a table with zip codes, and I need to find the zip codes where all my records indicate there are only commercial entities.

table

ID  zip   entity
1   1111   F
2   1111   R
3   1111   C
4   1112   C
5   1112   C
6   1112   C
7   1113   Z
8   1113   S
9   1113   X

Return value

1112

Answer 1

You want the zip codes that only have entity 'C'. An easy way to check this is to look at the minimum and maximum value for entity; both must be 'C'.

select zip
from mytable
group by zip
having min(entity) = 'C' and max(entity) = 'C';

Answer 2

A couple of cute ways to get what you want have been presented. Personally, I like sticking with straightforward approaches unless performance demands otherwise. This seems like the clearest to me:

SELECT DISTINCT T1.zip
FROM MyTable T1
WHERE
    NOT EXISTS (SELECT * FROM MyTable T2 WHERE T2.zip = T1.zip AND T2.entity <> 'C')

The advantage of this, IMO, is that it's clear from looking at the code what it is trying to do, so when you look at the code again in six months you're not left scratching your head.

Answer 3

Select all commercial zip codes minus non commerical zip codes

For the diversity of solution purposes, I submit this solution.

The minus operator removes any zip codes which have both commercial and non-commerical entities.

SCOTT@db>SELECT
  2      zip
  3  FROM
  4      tbl
  5  WHERE
  6      entity = 'C'
  7  GROUP BY
  8      zip
  9  MINUS
 10  SELECT
 11      zip
 12  FROM
 13      tbl
 14  WHERE
 15      entity != 'C'
 16  GROUP BY
 17      zip;
ZIP    
1112   

Answer 4

Also, this maybe an alternative ( p^q' logic ):

 select zip
   from mytable
  group by zip
 having sign(sum( decode(entity,'C',1,0) ) ) * ( 1 - sign(sum( decode(entity,'C',0,1) ) )) = 1;

D E M O 1

D E M O 2 ( go&press execute )