I have a set of tableA
Name | City |
---|---|
Paulo | Rome |
Rudy | Singapore |
Ming | Singapore |
Takeshi | Tokyo |
Judy | Jakarta |
Yuki | Tokyo |
Steve | Singapore |
I want to make sure that person from Berlin, Singapore, and Tokyo, all 3 of those cities are present in the table
I have this SQL query to check that each city is present
select a.*
from (
select *,
row_number() over (partition by city ) as rn
from tableA
where city in (Berlin, Tokyo, Singapore)
) a
where rn = 1
With this query I can find out that at least 1 person from those countries is present, while actually there's none from Berlin. What I want is the query can validate that all of cities that in IN
condition are present. It will return True
if all of them is present and False
if one of them is missing. Is it possible to do so? Suggestion for simpler query also appreciated.
You can compare count of cities in IN(...) and your query output rows count
select case when count(*)=3 then 'true' else 'false' end res
from (
select *,
row_number() over (partition by city order by name) as rn
from tableA
where city in ('Berlin', 'Tokyo', 'Singapore')
) a
where rn = 1
res='false'
OR simpler
select case when count(*)=3 then 'true' else 'false' end res
from(
select distinct city
from tableA
where city in ('Berlin', 'Tokyo', 'Singapore')
) a
With @jarlh suggestion
select case when count(distinct city)=3 then 'true' else 'false' end res
from tableA
where city in ('Berlin', 'Tokyo', 'Singapore')
I can't check it on amazon-athena, but it's a beautiful solution!