SQL Select rows on different tables that have the same FK values

Question

I need to create a query that list every people able to speak in a country with all the required languages. I have 3 tables ( country / Language / People) The problem is that I don't have a link between the table Country and People but each table have a foreign key "Language_Id". My goal is to list every people that have as much language as the Country . Here is an example :

Table People :

     ID   Name    Language_ID  
1.   1    Paul    1
2.   2    Paul    2
3.   3    Max     1
4.   4    Ben     2
5.   5    Paul    3
6.   6    Ben     3

Table Language :

     ID   Name
1.   1    English
2.   2    Dutch
3.   3    French

Table Country :

     ID   Name         Language_ID
1.   1    France       3
2.   2    Netherlands  1
3.   3    Netherlands  2
4.   4    Belgium      2
5.   5    Belgium      3

Result table :

       Name    Country_Name
1.     Paul    France
2.     Paul    Netherlands
3.     Paul    Belgium
4.     Ben     Belgium
5.     Ben     France

So , Max is excluded from the results because he doesn't have all the required languages.

I am running SQL Server 2016 Std edition.

Could you help me to find a solution ?

Thank you !

Answer 1

You can use a join and aggregation. The one tricky part is counting the number of languages within a country to be sure that a person speaks all of them:

select p.name, c.name, c.num_languages
from people p join
     (select c.*, count(*) over (partition by name) as num_languages
      from country c
     ) c
     on p.language_id = c.language_id
group by p.name, c.name, c.num_languages
having count(*) = c.num_languages;