How can I avoid duplicate usernames while joining 2 tables

Question

I have two tables related to user information: Table 1(Users) contains columns like id,username and email Table 2(details) contains columns like user_id,degree_name and degree when I am joining these two tables, result I am getting is:

name         degree_name      degree

john doe      Metric          Science
john doe      Fsc             Engineering  
john doe      Bsc             BSCS
herald        Metric          Science
Matt          Metric          Science

In above records "john doe" is repeating 3 times. what should be not expected result. The result I want here is and also I know can be achievable by using find_in_set() is:

0: {name: john doe
    details: {0: {degree: "Science",degree_name: "Metric"}
              1: {degree: "Fsc",degree_name: "Engineering  "}
              2: {degree: "Science",degree_name: "BSCS"}}}
1: {name: herald
    details: {0: {degree: "Science",degree_name: "Metric"}}}
2: {name: Matt
    details: {0: {degree: "Science",degree_name: "Metric"}}}

Is there anybody who guide me for the right query?

Mysql (and sql generally) is not designed to pretty print if you have an application (eg php) you should do it there. — P.Salmon, Apr 19 '19 at 12:27
Yes I know that this is achievable through php but the thing is, Mysql will get all records then we will loop and get records from there. that is not good — ahmad ashfaq, Apr 19 '19 at 12:29
Also it’s possible to generate JSON data directly in MySQL, which i think you are after here? — Raymond Nijland, Apr 19 '19 at 12:53

score 0 · Answer 1 · edited Apr 19 '19 at 13:03

0

You can use group_concat for degree and degree_name. eg . :

SELECT name, group_concat(degree), group_concat(degree_name) FROM Table1,Table2 WHERE Table1.id=Table.User_id. GROUP BY Table1.id

It will give you result with one row for one user.

Jon Doe | Metric,Fsc,Bsc | Science,Eng,BCSc.

edited Apr 19 '19 at 13:03

answered Apr 19 '19 at 12:34

Madhura

163
1
14

I know group_concat() will combine records into one row but as mentioned above, I want an array like details for user details table. – ahmad ashfaq Apr 19 '19 at 12:36

score 0 · Answer 2 · answered Apr 22 '19 at 13:12

SELECT  u.username, CONCAT('{ "array": [',
               GROUP_CONCAT(CONCAT('{"degree_name": "',
                                   d.degree_name,
                                   '", "degree": "',
                                   d.degree,
                                   '"}'
                            ) separator ','),
               ']}') As details
FROM details d
        INNER JOIN users u
            ON FIND_IN_SET(u.id, d.user_id) > 0
GROUP BY u.id;

This query will provide the answer as expected. Just need a little over head to json parse records after getting results.

How can I avoid duplicate usernames while joining 2 tables

2 Answers2