select a.id_product, a.id_category, max(case when code='en' then [name] else null end) [name in english],
max(case when code='es' then [name] else null end) [name in spanish]
from products a join products_lang b on a.id_product=b.id_product
join languages c on b.id_lang=c.id_lang
group by a.id_product, a.id_category

编辑：在进一步的评论澄清了这个问题后，我原来的答案不再适用。这本质上是一个枢轴操作。因为mysql在数据透视方面有点不太优雅，所以我使用了 GROUP BY 以及 WITH ROLLUP . 我还添加了一些孤立和缺失的数据，以演示查询将如何处理这些数据。
sql小提琴
mysql 5.6架构设置：

CREATE TABLE products (id_product int, id_category int) ;
CREATE TABLE products_lang ( id_product int, id_lang int, name varchar(20)) ;
CREATE TABLE languages (id_lang int, code varchar(5)) ;

INSERT INTO products (id_product, id_category)
SELECT 1 AS id_product, 1 AS id_category UNION ALL 
SELECT 2, 1 UNION ALL
SELECT 3, 2
;

INSERT INTO products_lang (id_product, id_lang, name)
SELECT 1 AS id_product, 1 AS id_lang, 'Car' AS name UNION ALL
SELECT 1 , 2 , 'Auto' UNION ALL
SELECT 2 , 1 , 'Car' UNION ALL
SELECT 2 , 2 , 'Auto' UNION ALL
SELECT 3 , 7 , 'voiture' UNION ALL
SELECT 4 , 9 , 'autoa'
;

INSERT INTO languages (id_lang, code)
SELECT 1 AS id_lang, 'en' AS code UNION ALL
SELECT 2, 'es' UNION ALL
SELECT 7, 'fr'
;

查询1：

SELECT s1.id_product
  , s1.id_category
  , s1.NameInEnglish
  , s1.NameInSpanish
  , s1.NameInFrench
FROM (
SELECT 
    p.id_product
  , p.id_category
  , MAX(IF(pl.id_lang=1, CONCAT(pl.name, '[', l.code, ']'),'')) AS NameInEnglish
  , MAX(IF(pl.id_lang=2, CONCAT(pl.name, '[', l.code, ']'),'')) AS NameInSpanish
  , MAX(IF(pl.id_lang=7, CONCAT(pl.name, '[', l.code, ']'),'')) AS NameInFrench
FROM products p 
LEFT OUTER JOIN products_lang pl ON p.id_product = pl.id_product
LEFT OUTER JOIN languages l ON pl.id_lang = l.id_lang
GROUP BY p.id_product
WITH ROLLUP
) AS s1
WHERE s1.id_product IS NOT NULL

结果：

| id_product | id_category | NameInEnglish | NameInSpanish | NameInFrench |
|------------|-------------|---------------|---------------|--------------|
|          1 |           1 |       Car[en] |      Auto[es] |              |
|          2 |           1 |       Car[en] |      Auto[es] |              |
|          3 |           2 |               |               |  voiture[fr] |