SQL - Select where parameter A is one of multiple values and parameter B is equal

Question

Say we have table with columns A and B.

I need to select rows, in which parameter A is one of the set ARRAY values and parameters B at these rows are equal, while there is no other row with parameter A, which is not in ARRAY, but it's row has equal parameter B to those which are in ARRAY.

For instance we have table:

John    1
Andrew  1
John    2
Paul    2
John    3
Andrew  3
Paul    3

and I need to select rows according to ARRAY = (John, Andrew), so the result should be only first two rows (parameters A are both in ARRAY, parameters B are equal and there is no other row with equal parameter B):

John    1
Andrew  1

Can you help me with SQL SELECT syntax for that (MySQL)?

PS: Number of arguments in ARRAY can vary.

PS2: Result should be only rows, where every single item from ARRAY is present. So for example if ARRAY = (John, Andrew, Paul), result should be only:

John    3
Andrew  3
Paul    3

Solution 1

SELECT *
FROM YourTable
WHERE a IN ('John', 'Andrew', 'Paul')
AND b NOT IN (
    SELECT b
    FROM YourTable
    WHERE a NOT IN ('John', 'Andrew', 'Paul')
) AND b IN (
    SELECT b
    FROM YourTable
    WHERE a IN ('John', 'Andrew', 'Paul')
    GROUP BY b
    HAVING COUNT(*) = 3)

The COUNT(*) qualifier should be the same as the number of elements in the set. This assumes that names are not repeated within a particular b value.

DEMO

This is practically a word-for-word translation of your description into SQL.

Solution 2

I approach these problems using group by with a having clause. The following gets the B values that meet your conditions

select b
from yourtable t
group by b
having count(distinct a) = 3 and
       count(distinct case when a in ('John', 'Andrew', 'Paul') then a end) = 3;

You can then choose the groups themselves by joining back to this list:

select t.*
from yourtable t join
     (select b
      from yourtable t
      group by b
      having count(distinct a) = 3 and
             count(distinct case when a in ('John', 'Andrew', 'Paul') then a end) = 3
     ) bt
     on t.b = bt.b;

EDIT:

Actually, there is a similar approach using group_concat():

select t.*
from yourtable t join
     (select b, group_concat(distinct a order by a) as acols
      from yourtable t
      group by b
      having acols = 'Andrew,John,Paul'
     ) bt
     on t.b = bt.b;

This approach makes it possible to put the names in once. You just have to be careful that they are in alphabetical order.

Comments

[wildfireheart]

Thank you very much, but it seems I wasn't exact enough. Your solution works, but it is slightly different from what I need. I updated original post with PS2.

[wildfireheart]

Works! Thank you very very much!