在我看来，最直接的方法包括两个到user\u languages表的连接，以及每个到langauges表的连接。

select  usr.User, lg1.Language PriLang, lg2.Language SecLang 
from users usr
 left join user_languages ul1
  on ul1.userId = usr.userId
   and ul1.Fluency='Primary'
 left join user_languages ul2
  on ul2.userId = usr.userId
   and ul2.Fluency='Secondary'
 left join languages lg1
  on lg1.languageId = ul1.languageId
 left join languages lg2
  on lg2.languageId = ul2.languageId

一个选项使用条件聚合：

select 
    u.user,
    max(case when l.fluency = 'Primary' then l.language end) as pri_lang,
    max(case when l.fluency = 'Secondary' then l.language end) as sec_lang
from users u 
left join user_languages ul on ul.user_id = u.user_id
left join languages l on l.language_id = ul.language_id
group by u.user_id, u.user

也许像这样的事情取决于你的设置；这将使用子查询关闭：

SELECT u.User,
       (SELECT lt.Language
        FROM Language_table lt
            JOIN UserLanguage_table ult ON lt.LanguageId = ult.LanguageId 
            JOIN User_table u ON ult.UserId = u.UserId
        WHERE lt.Language_Fluency = 'primary'
            AND ult.UserId = u.UserId
            AND lt.LanguageId = ult.LanguageId) AS primary_lang,
       (SELECT lt.Language
        FROM Language_table lt
            JOIN UserLanguage_table ult ON lt.LanguageId = ult.LanguageId 
            JOIN User_table u ON ult.UserId = u.UserId
        WHERE lt.Language_Fluency = 'secondary'
            AND ult.UserId = u.UserId
            AND lt.LanguageId = ult.LanguageId) AS secondary_lang
FROM User_table u;