How can I find which attribute value contains only one value type?

Question

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

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 zip codes to only have entity 'C'. An easy way to verify this is to look at the minimum and maximum values ​​for the entity ; both must be "C".

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

Answer 2

Several cute ways to get what you want have been introduced. Personally, I like to follow simple approaches unless performance requires otherwise. This seems to me the clearest:

 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 does not see the code, what it is trying to do, so when you look at the code again after six months, you will not scratch your head.

Answer 3

Select all commercial postal codes minus non-commercial postal codes

For a variety of purposes, I present this solution.

The minus operator deletes any postal codes that have both commercial and non-commercial objects.

 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

Alternatively, it could be 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; 

Demo 1

DEMO 2 (go & press execute)