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.
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;