MySQL query. Select data from three tables

I have three tables:

language (id,lang_name, published)

translators (id, name,surname, phone, email, published)

trans_lang_rel(language and translator relation. one translator can translate from one or more languages)

id, trans_id, lang_id

Data in tables:

language

1 English 1

2 Russian 1

3 German 1

translators

1 Name1 Surname1 999 name1.surname1@gmail.com 1

2 Name2 Surname2 888 name2.surname2@gmail.com 1

trans_lang开发者_运维知识库_rel

1 1 1

2 1 2

3 2 1

So I have to select data from these three tables.

Result should be like this:

Name1 | Surname1 | English,Russian | 999 | name1.surname1@gmail.com

I've tried something like this but I got strange results:

SELECT t.*, 
GROUP_CONCAT(l.lang_name SEPARATOR ", ") AS language 
FROM translators AS t, 
trans_lang_rel AS tlr 
LEFT JOIN language AS l ON l.id = tlr.lang_id ORDER BY t.id 

Your help would be appreciated.

Try something like

SELECT  t.name,
        t.surname,
        GROUP_CONCAT(l.lang_name SEPARATOR ", "),
        t.phone,
        t.email
FROM    translators t INNER JOIN
        trans_lang_rel tlr  ON  t.id = tlr. trans_id INNER JOIN
        language l  ON  tlr.lang_id = l.id
GROUP BY    t.name,
            t.surname,
            t.phone,
            t.email

SELECT
  translators.name,
  translators.surname,
  GROUP_CONCAT(language.name SEPARATOR ", ") AS language,
  translators.phone,
  translators.email
FROM
  translators
LEFT OUTER JOIN
  trans_lang_rel
ON
  trans_lang_rel.trans_id = translators.id
LEFT OUTER JOIN
  language
ON
  trans_lang_rel.lang_id = language.id

the way you have joined translator and trna_lang_rel wasn't good. You made a full join since you never but any ON clause

SELECT t.*, 
GROUP_CONCAT(l.lang_name SEPARATOR ", ") AS language 
FROM translators t 
JOIN trans_lang_rel tlr ON t.id = trans_id
LEFT JOIN language AS l ON l.id = tlr.lang_id
ORDER BY t.id