MySQL on redundant rows

Question

A user has a student (one to one) and a student can have many languages and hobbies (both times many to many).

If I run this query,

SELECT email, hobbies.name, languages.name
FROM users
INNER JOIN students
    ON users.id = students.user_id
LEFT OUTER JOIN languages_student
    ON students.id = languages_student.student_id
    INNER JOIN languages
        ON languages_student.language_id = languages.id
LEFT OUTER JOIN hobbies_student
    on students.id = hobbies_student.student_id
    INNER JOIN hobbies
        ON hobbies_student.hobbie_id = hobbies.id
WHERE users.id = 6

I get this result set:

If I add another language to a student, I get six rows in the result set. Is there a way of combining the second and third columns in order to get something more compact and not redundant? Can each hobby appear just once and get a NULL in languages when they run out?

Can [this](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) help? — Mateusz, May 24 '16 at 15:26
@Mateusz That looks good. I guess I then would have to re separate the CSV in my controller (or view?). I'm a bit confused. How can I adapt that into something like my existing query? — Sebastian M, May 24 '16 at 15:31
@JulianM. See Mike Brant's answer. If you're using MySQL, there's a function called GROUP_CONCAT - that's what you need. — Mateusz, May 24 '16 at 15:50
_Can each hobby appear just once and get a NULL in languages when they run out?_ There is a way, but it is **very** convoluted and involves borderline abuse of session variables, and temp tables or multiple subqueries; all for a result that implies a false relation between languages and hobbies. — Uueerdo, May 24 '16 at 16:20

Mike Brant · Accepted Answer · 2016-05-24T16:04:45.003

There are a couple of approaches to being able to aggregate this information. One is to do this in your application logic based on the type of result set you currently show. This might be done be reading the rows from the result set into an appropriate data structure you can then use in your application to display this information.

The second approach is to use GROUP_CONCAT() to concatenate values within same group (in this case email name) into a single row. That might lead to a results set like this:

shields.katlynn@swaniaski.biz    Endurance Sports,Golf    Balochi,Assamesse

This might mean that in your application, you would need to split apart the data in each row to get to individual values.

An example of how you might write the query to get the above result would be:

SELECT
    email,
    GROUP_CONCAT(DISTINCT hobbies.name) AS hobbies,
    GROUP_CONCAT(DISTINCT languages.name) AS languages
FROM users
INNER JOIN students
    ON users.id = students.user_id
LEFT OUTER JOIN languages_student
    ON students.id = languages_student.student_id
    INNER JOIN languages
        ON languages_student.language_id = languages.id
LEFT OUTER JOIN hobbies_student
    on students.id = hobbies_student.student_id
    INNER JOIN hobbies
        ON hobbies_student.hobbie_id = hobbies.id
WHERE users.id = 6
GROUP BY email

In either case, you will need some sort of post-retrieval data processing in your application.

Thanks! That looks good. Hoewer my result set now looks like: `Endurance Sports,Golf,Endurance Sports,Golf` `Balochi,Balochi,Assamesse,Assamesse` Is there a way of adding a `DISTINCT` somewhere in your code? — Sebastian M, May 24 '16 at 15:52
@JulianM. Updated my example query. You do exactly what you suggest and simply add `DISTINCT` keyword. You can also do things like add an `ORDER BY` clause if you want to order the results for concatenation or change the separator to something other than default comma. Check out the MySQL documentation for more usage information. — Mike Brant, May 24 '16 at 16:06

MySQL on redundant rows

1 Answers1