CODEWITHSUNDEEP
mysqldatabase
Sebastian M
Sebastian M

Reputation: 491

MySQL on redundant rows

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:

Result

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?

Upvotes: 0

Views: 73

Answers (1)

Mike Brant
Mike Brant

Reputation: 71422

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:

[email protected]    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.

Upvotes: 1

Related Questions