mysql - 检查记录是否具有IN运算符的所有值(Check if a record has all of the values of IN operator)

Question

There are three tables which are like this:

(有三个表，如下所示：)

S  (sid, sname, scity)
P  (pid, pname, color, weight)
SP (sid, pid, quantity)

The question is to return those sname(s) which has(have) all of the pid(s) with blue color

(问题是返回具有所有蓝色的pid的那些sname)

At first I wrote this one:

(首先，我写了这个：)

SELECT S.sname FROM S INNER JOIN SP ON S.sid = SP.sid 
INNER JOIN P ON SP.pid = P.pid 
WHERE color = 'blue';

which of course is not right, because it returns those sname(s), even it has just one P with blue color.

(当然这是不对的，因为即使它只有一个带有蓝色的P，它也会返回这些sname。)

My second query is this one:

(我的第二个查询是这个：)

SELECT S.sname FROM S INNER JOIN SP ON S.snum = SP.snum 
INNER JOIN P ON SP.pnum = P.pnum 
WHERE SP.pnum IN
(SELECT pnum FROM P WHERE color != 'blue');

This is also not right, because it seems that IN operator acts like multiple OR conditions.

(这也不对，因为看起来IN运算符的行为类似于多个OR条件。)

Would you please let me know how can I set a condition which instead of OR , acts like AND ?

(您能否让我知道如何设置一个条件，该条件代替OR而是像AND ？)

  ask by drdn translate from so

Answer 1

I think you want group by and having .

(我想你想group by和having 。)

I think this is what you want:

(我认为这是您想要的：)

select sp.sid
from sp join
     p
     on sp.pid = p.pid
where p.color = 'blue'
group by sp.sid
having count(distinct sp.pid) = (select count(*) from p p2 where p2.color = 'blue');

This returns the sid .

(这将返回sid 。)

You can get the name with an additional joi n.

(您可以通过附加的joi来获得名称。)