DB2/400 SQL subqueries

Question

I want to make sure that warehouses C and D are both in the same City.

Is there simpler way to do this?

Table:

City Warehouse
1      A        
1      B
2      C
2      D 
3      E
3      F 

SQL:

WITH subquery AS(
  SELECT DISTINCT WHM.City FROM WHM WHERE  
  WHM.Warehouse = 'C' or WHM.Warehouse ='D'
  GROUP BY WHM.City 
  ORDER BY WHM.City ) 
SELECT count(*) FROM subquery 

If response = 1, then True

Answer 1

This is a minor improvement on what you already have, but you could select the distinct count and avoid the unnecessary subquery:

SELECT COUNT(DISTINCT City)     -- returns 1 for a single shared city
FROM WHM
WHERE Warehouse IN ('C', 'D');

Note that GROUP BY is not needed as we want the distinct count across the entire table.

Answer 2

Direct query

select (select distinct 1 from WHM w2 where w2.city=w1.city and w2.Warehouse='D') response
from WHM w1
where w1.Warehouse='C'
;

returns 1 or null

distinct 1 - for case where more than 1 Warehouse='D' in City (if possible).