CODEWITHSUNDEEP
mysqldatabasejoinrelational-databaseone-to-many
The Beginner
The Beginner

Reputation: 3

One to many relation in MYSQL produces wrong results

I have two tables- students and subjects. Student table stores list of all student and subject table stores all the subjects that these students have been enrolled into.

CREATE TABLE students
    (`id` int, `name` varchar(7));

INSERT INTO students
    (`id`, `name`)
VALUES
    (1, 'Jason'),
    (2, 'Matt'),
    (3, 'Abram')
    ;

CREATE TABLE subjects
    (`id` int,`student_id` int, `subject` varchar(15));

INSERT INTO subjects
    (`id`,`student_id`, `subject`)
VALUES
    (1,1, 'Math'),
    (2,1, 'Physics'),
    (3,2, 'Chemistry'),
    (4,2, 'Math'),
    (5,2, 'English'),
    (6,3, 'Chemistry')
    ;

And upon executing the following query

SELECT STUD.id,STUD.name,SUB.subject 
FROM students AS STUD 
LEFT JOIN subjects AS SUB
ON STUD.id=SUB.student_id
;

gives a result set like this (which is not what I am trying to get)

++++++++++++++++++++++++++++++++++++
id     | name  | subject           |
++++++++++++++++++++++++++++++++++++
| 1    | Jason | Math              |
------------------------------------
| 1    | Jason | Physics           |
------------------------------------
| 2    | Matt  | Chemistry         |
------------------------------------
| 2    | Matt  | Math              |
------------------------------------
| 2    | Matt  | English           |
------------------------------------
| 3    | Abram | Chemistry         |
------------------------------------

The students gets repeated in the list.

And when I try

SELECT STUD.id,STUD.name,SUB.subject 
FROM students AS STUD 
LEFT JOIN subjects AS SUB
ON STUD.id=SUB.student_id
GROUP BY STUD.id
;

I get (which is again wrong)

++++++++++++++++++++++++++++++++++++
id     | name  | subject           |
++++++++++++++++++++++++++++++++++++
| 1    | Jason | Math              |
------------------------------------
| 2    | Matt  | Chemistry         |
------------------------------------
| 3    | Abram | Chemistry         |
------------------------------------

My Desired result looks like this:

++++++++++++++++++++++++++++++++++++++++++++++++
id     | name  | subject                       |
++++++++++++++++++++++++++++++++++++++++++++++++
| 1    | Jason | Math, Physics                 |
------------------------------------------------
| 2    | Matt  | Chemistry,Math ,English       |
------------------------------------------------
| 3    | Abram | Chemistry                     |
------------------------------------------------

I am a novice in Relational databases. Why is group by not giving correct result? Thanks in advance.

SQL FIDDLE

Upvotes: 0

Views: 49

Answers (1)

Vishal Jain
Vishal Jain

Reputation: 1940

You can achieve this by

SELECT STUD.id,STUD.name,GROUP_CONCAT(SUB.subject) as subject
FROM students AS STUD 
LEFT JOIN subjects AS SUB
ON STUD.id=SUB.student_id
GROUP BY STUD.id;

Actually it gives correct result when you have group by some column in sql it will returns only first record in group so for your desired result you needs to used GROUP_CONCAT function which return all comma separated values in given column.

Upvotes: 1

Related Questions