most efficient sql for retrieval

Question

I have this table

 ----------------
|   X    |   Y   |
 ----------------
|   a    |   1   |
|   c    |   6   |
|   e    |   3   |
|   d    |   6   |
|   c    |   4   |
|   b    |   1   |
|   a    |   5   |
|   g    |   1   |
 ----------------

When I'm given an array [c,d] I need to find "6" in the table above. I.e. for every set of elements I need to find the Y value that is shared by all the elements in the set but only if there is no other element (i.e. an element that's not in the given array) that shares that value. The number of elements in the array has no theoretical limits.

More examples: for [a,b,c] I need to find nothing. For [a,b] I also need to find nothing (because g also has an entry for Y = 1, so for [a,b,g] I do need to find "1").

I could of course iterate over the array, query by query, but that seems such an inefficient way of doing it. What's the best way of doing this in SQL? Thank you.

Answer 1

Here's a way by having your "query" values in a separate table.

create table t ( x varchar(1), y int);

insert into t (x, y) values ('a', 1);
insert into t (x, y) values ('c', 6);
insert into t (x, y) values ('e', 3);
insert into t (x, y) values ('d', 6);
insert into t (x, y) values ('c', 4);
insert into t (x, y) values ('b', 1);
insert into t (x, y) values ('a', 5);
insert into t (x, y) values ('g', 1);

create table q ( x varchar(1) );

insert into q (x) values ('a');
insert into q (x) values ('b');

select a.y from
(
   select t.y
     from t join q on (t.x = q.x)
   group by t.y
   having count(*) = (select count(*) from q)
) a 
join t on (a.y = t.y) 
group by a.y
having count(*) = (select count(*) from q)

Here's an example SQLFiddle.

This assumes that you can't have duplicate combinations.

If you want to do it without the second table, you can replace the select count(*) with the number of values you are matching to in an IN list, and instead of doing the join on the inner subquery, use a where clause.

select a.y from
(
select t.y
from t
  where t.x in ('c', 'd')
group by t.y
having count(*) = 2
) a 
join t on (a.y = t.y) 
group by a.y
having count(*) = 2