Mysql SELECT in SELECT

I have a table like this :

+--------+---------------------------------------+
| CandId | Speak                                 |
+--------+---------------------------------------+
|      1 | English                               |
|      1 | Spanish                               |
|      2 | English                               |
|      2 | Spanish                               |
|      3 | Dutch                                 |
|      3 | English                               |
|      4 | Dutch                                 |
|      4 | Spanish                               |
|      4 | German                                |
+--------+---------------------------------------+

I’m trying to make a query that would for instance get the CandId of people that speaks English and Spanish (not or Spanish).

So in the specific case the query would show 1 and 2.

It’s surely very easy but I even can’t imagine how to do this.

Many thanks by advance for your help.

Answer

The column name CandId suggests that you have a Cand table containing the people. You can then select from that table and look up the languages with IN or EXISTS:

select *
from cand
where candid in (select candid from cand_languages where speak = 'English')
  and candid in (select candid from cand_languages where speak = 'Spanish')
order by candid;

With a large table, you would provide an index to get the lookup fast:

create index idx on cand_languages(speak, candid);