Select all rows only if the count of rows found matches the number of "arguments" used-CodePudding

Let's assume I have the following table my_table:

id	name
1	Tyler
2	Earl
3	Frank
4	Jasper

I'm using this query:

SELECT mt.* FROM my_table AS mt WHERE mt.name IN ('Earl', 'Jasper');

that outputs

id	name
2	Earl
4	Jasper

However, I only want to return something when the count of rows is equal to the count of names. So

SELECT mt.* FROM my_table AS mt WHERE mt.name IN ('Earl', 'John');

should output an empty query, instead of

id	name
2	Earl

TL;DR: I want a query to return all rows only if the count of rows found matches the number of arguments used.

CodePudding user response：

Try this :

WITH list AS
( SELECT *
    FROM my_table AS t
   RIGHT JOIN unnest(array['Earl', 'John']) AS a(val)
      ON a.val = t.name
)
SELECT *
  FROM list
 WHERE NOT EXISTS (SELECT 1 FROM list WHERE id IS NULL)

see the test result in dbfiddle.

CodePudding user response：

You can collect all found names and then check if that matches the number you want:

select id, name
from (
  select *, 
         array_agg(name) over () as all_names
  from the_table
  where name in ('Earl', 'Frank')
) t
where cardinality(all_names) = 2

CodePudding user response：

If you are all right with the syntax, you can pass your query names in as a text array:

with inlist as (
  select '{"Earl", "Jasper"}'::text[] as q
), init_match as (
  select t.id, t.name, cardinality(i.q) as qlen, count(1) over () as matchlen
    from inlist i
         join my_table t
           on t.name = any(i.q)
)
select * 
  from init_match
 where qlen = matchlen;

working fiddle