MySQL query. Select data from three tables

Question

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 [email protected] 1
2 Name2 Surname2 888 [email protected] 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 | [email protected]

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.

Adriaan Stander · Accepted Answer · 2010-12-15 08:40:58Z

1

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

answered Dec 15, 2010 at 8:40

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Grossman · Accepted Answer · 2010-12-15 08:39:25Z

0

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

answered Dec 15, 2010 at 8:39

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Comments

RageZ · Accepted Answer · 2010-12-15 08:53:19Z

0

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

edited Dec 15, 2010 at 8:53

answered Dec 15, 2010 at 8:38

RageZ

27.3k12 gold badges72 silver badges76 bronze badges

Collectives™ on Stack Overflow

MySQL query. Select data from three tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related