sqlamazon-athenaprestotrinojupyterhub

How to validate that each value of 'IN' statement are present in the SQL query result?


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.


Solution

  • 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
    

    Example

    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!