mysql - SQL - Duplicated values in select concat with table joins -

mysql - SQL - Duplicated values in select concat with table joins -

scenario

i want obtain registered languages user has along user details, current problem values getting duplicated when group_concat target column. can explain me why values beingness duplicated.

my tables

// users +------+-------------+------------+----------------+--------------+ | id | firstname | lastname | email | created_at | +------+-------------+------------+----------------+--------------+ | 10 | john | doe | john@doe.com | 2014-10-21 | +------+-------------+------------+----------------+--------------+ // teachers +------+-----------+------------------+-------------------+ | id | user_id | years_teaching | months_teaching | +------+-----------+------------------+-------------------+ | 35 | 10 | 3 | 6 | +------+-----------+------------------+-------------------+ // teacher_languages +------+--------------+---------------+----------+---------+ | id | teacher_id | language_id | status | views | +------+--------------+---------------+----------+---------+ | 52 | 35 | 5 | 1 | 80 | +------+--------------+---------------+----------+---------+ | 53 | 35 | 7 | 1 | 40 | +------+--------------+---------------+----------+---------+ // translators +------+-----------+------------------------+---------------------+ | id | user_id | certified_translator | accept_travelling | +------+-----------+------------------------+---------------------+ | 23 | 10 | 0 | 1 | +------+-----------+------------------------+---------------------+ // translator_languages +------+-----------------+---------------+----------+---------+ | id | translator_id | language_id | status | views | +------+-----------------+---------------+----------+---------+ | 52 | 23 | 5 | 1 | 27 | +------+-----------------+---------------+----------+---------+ | 53 | 23 | 7 | 1 | 82 | +------+-----------------+---------------+----------+---------+ // languages +------+-----------------+------------+ | id | language_text | language | +------+-----------------+------------+ | 5 | english language | en | +------+-----------------+------------+ | 7 | french | fr | +------+-----------------+------------+

current query

this query produces duplicate values

select u.*, // teacher languages group_concat(l.language_text) teacher_languages, group_concat(tl.status) teacher_languages_status, group_concat(tl.views) teacher_language_views // translator languages group_concat(trl_txt.language_text) translator_languages, group_concat(trl.status) translator_languages_status, group_concat(trl.views) translator_language_views users u left bring together teachers t on t.user_id = u.id left bring together teacher_languages tl on tl.teacher_id = t.id left bring together languages l on l.id = tl.language_id left bring together translators tr on tr.user_id = u.id left bring together translator_languages trl on trl.teacher_id = t.id left bring together languages trl_txt on trl_txt.id = trl.language_id grouping u.id order u.created_at

results

[0] => [id] => 10 [firstname] => 'john' [lastname] => 'doe' [email] => 'john@doe.com' [created_at] => '2014-10-21' [teacher_languages] => 'english, english, french, french' [teacher_language_status] => '1,1,1,1' [teacher_language_views] => '80,40,80,40' [translator_languages] => 'english, french, english, french' [translator_language_status] => '1,1,1,1' [translator_language_views] => '27,82,27,82'

q: can explain me why values beingness duplicated.

a: improve understand why values duplicated, run query without group_concat , group by. query produces sort of cartesian product: because each teacher , each translator has 2 languages in result possible combinations:

teacher_languages translator_languages ----------------- -------------------- english language english language english french french english language french french

group_concat concatenates column values , get:

[teacher_languages] => 'english, english, french, french' [translator_languages] => 'english, french, english, french'

one of possible solutions avoid duplicates utilize subqueries:

select u.*, a.teacher_languages, a.teacher_languages_status, a.teacher_language_views, b.translator_languages, b.translator_languages_status, b.translator_language_views users u left bring together ( select t.user_id, -- teacher languages group_concat(tl_txt.language_text) teacher_languages, group_concat(tl.status) teacher_languages_status, group_concat(tl.views) teacher_language_views teachers t left bring together teacher_languages tl on tl.teacher_id = t.id left bring together languages tl_txt on tl_txt.id = tl.language_id grouping t.user_id ) on a.user_id = u.id left bring together ( select tr.user_id, -- translator languages group_concat(trl_txt.language_text) translator_languages, group_concat(trl.status) translator_languages_status, group_concat(trl.views) translator_language_views translators tr left bring together translator_languages trl on trl.teacher_id = tr.id left bring together languages trl_txt on trl_txt.id = trl.language_id grouping tr.user_id ) b on b.user_id = u.id order u.created_at

mysql