MySql: Select entries with same dependencies in junction table

Question

These are my tables:

 language             language_person                   person
 +----+----------+    +----+-----------+-----------+    +----+-----------------+
 | id | name     |    | id | languageId| personId  |    | id | name            |
 +----+----------+    +----+-----------+-----------+    +----+-----------------+
 | 1  | english  |    | 1  | 1         | 1         |    | 1  | luca            |
 +----+----------+    +----+-----------+-----------+    +----+-----------------+
 | 2  | german   |    | 2  | 2         | 1         |    | 2  | sara            |
 +----+----------+    +----+-----------+-----------+    +----+-----------------+
 | 3  | italian  |    | 3  | 3         | 1         |    | 3  | michael         |
 +----+----------+    +----+-----------+-----------+    +----+-----------------+
                      | 4  | 3         | 2         |    
                      +----+-----------+-----------+ 
                      | 5  | 2         | 3         |    
                      +----+-----------+-----------+ 

Currently I'm loading with JPA a person and a list of the languages spoken by this person. Every person talks at least one language. Now I would like to implement a query, to get all the person that talks exactly and only the same languages. In case of 'Luca', I search someone that talks english AND german AND italian. When searching with a query like WHERE languageId IN (1, 2, 3) I obviously get all person back that talk at least one of the languages. Also when searching for someone like 'sara', I search for someone talking ONLY italian, no other language.

It's completely out of my understanding, so any help is higly appreciated.

Answer 1

I think the simplest way is to use aggregation and window functions:

select id, name
from (select p.id, p.name,
             group_concat(lp.languageid order by lp.languageid) as languageids,
             max(case when p.name = 'Luca' then group_concat(lp.languageid order by lp.languageid) end) over () as luca_languageids
      from language_person lp join
           person p
           on lp.personid = p.id
      group by p.id, p.name
     ) p
where languageids = luca_languageids;