mysql join repeats row, order by caused problem

Question

I am trying to fetch each class name, students in this class and subjects assigned to this class.

Here, Problem is caused by ORDER BY, I have assigned ORDER BY to student.id.

This causes subject rows duplication.

If ORDER BY is not assign, subject rows are fetched as expected, are not duplicate, but student row duplicates.

Table structure

Student

id  |   Rid |   class   |   name
================================
1   |   1   |   1       |   John
2   |   2   |   1       |   Harsita

Class

id  |   title
==============
1   |   One
2   |   two
3   |   three

Subject

id  |   title
===============
1   |   science
2   |   math
3   |   english
4   |   social
5   |   hpe

class_subject

id  |   class   |   subject
===========================
1   |   1       |   1   
2   |   1       |   2
3   |   1       |   3
4   |   1       |   4

Requirement is

class: One
Science | Math | English | Social | Hpe | 
John | Harsita

But what I get

if ORDER BY is assigned to student.id

class: One
Science | Math | English | Social | Hpe | Math | English | Social | Hpe | Science | 
John | Harsita |

if ORDER BY has not assigned to student.id

class: One
Science | Math | English | Social | Hpe | 
John | Harsita | John | Harsita | John | Harsita | John | Harsita | John | Harsita |

I've tried using GROUP BY to subject.id, it displays only one student (first row: John). Where is problem ? How can I solve without using subquery or GROUP_CONCAT

code - php and mysql (with ORDER BY)

//mysql query
    SELECT 
        DISTINCT class_subject.class, 
        subject.title AS sub,
        student.name AS stdt,
        class.title AS class
    FROM 
        student
    INNER JOIN class_subject ON class_subject.class=student.class
    INNER JOIN subject ON subject.id=class_subject.subject
    INNER JOIN class ON class.id=student.class
    WHERE
        student.class=:cid;

//php
    $class='';
    $stdt='';
    $sub='';
    $results = Array();
        while($row=$result->fetch(PDO::FETCH_ASSOC)){
            if ($row['class'] != $class){
                $class = $row['class'];
                echo "Class: ".$class."<br/>";
            }
            if ($row['sub'] != $sub){
                $sub = $row['sub'];
                echo $sub." | ";
            }
            $results[]=$row;
        }
        echo "<br/>";
        foreach ($results as $row) {
            if ($row['stdt'] != $stdt) {
                $stdt = $row['stdt'];
                echo $stdt." | ";
            }
        }

Nick · Answer 1 · 2018-09-03T11:01:32.230

1

Your problem is caused by all the JOINs. There are 4 subjects associated with class 1, and 2 students. So you will get 4*2 = 8 rows in your result. As you have seen, the result of the join will be either 2 sets of the 4 subject names, or 4 sets of the 2 student names. You can choose to deal with this in your PHP code, or since you are effectively grouping in the PHP code anyway, do the grouping in your query:

SELECT 
    c.title AS class,
    GROUP_CONCAT(DISTINCT s.title ORDER BY s.title SEPARATOR ' | ') AS subjects,
    GROUP_CONCAT(DISTINCT t.name ORDER BY t.name SEPARATOR ' | ') AS students
FROM class c
JOIN class_subject cs ON cs.class=c.id
JOIN subject s ON s.id=cs.subject
JOIN student t ON c.id=t.class
WHERE c.id=1
GROUP BY class

Output:

class   subjects                            students
One     english | math | science | social   Harsita | John

Note that you can specify an ORDER BY inside the GROUP_CONCAT to control the ordering of the values in the group.

edited Sep 03 '18 at 11:01

answered Sep 03 '18 at 10:55

Nick

138,499
22
57
95

Thanks for your answer, I want to know, is it possible without using `GROUP_CONCAT`. So I have used the word **without** `GROUP_CONCAT` in question. – Dipak Sep 03 '18 at 11:01
1

Sorry I missed that part of the question.Without `GROUP_CONCAT` you will end up with 8 rows of data for the sample data in your question, and you will have to process that in your PHP code. And it will get worse as you add more subjects and students to each class. The only other alternative would be 2 queries, one to select all subjects associated with a class, and the other to select all students associated with a class. – Nick Sep 03 '18 at 11:05
that's the point, I think for performance 2 queries would be better than `GROUP_CONCAT`, isn't it ? because`GROUP_CONCAT()` invokes the group-by logic and creates temporary tables, which can be a big negative for performance, isn't it ? – Dipak Sep 03 '18 at 11:28
I don't think `GROUP_CONCAT` necessarily causes that big a performance impact. But it's probably one of those things where you need to benchmark it in your own environment. However given how easy it is for you to achieve what you want with 2 simple queries, that probably would be faster. – Nick Sep 03 '18 at 13:06

score 1 · Accepted Answer · answered Sep 03 '18 at 10:57

1

looking to your expected result sampel seem that you need an aggregated result for name and subject

SELECT 
    group_concat( subject.title) AS sub,
    group_concat(student.name) AS stdt,
    class.title AS class
FROM 
    student
INNER JOIN class_subject ON class_subject.class=student.class
INNER JOIN subject ON subject.id=class_subject.subject
INNER JOIN class ON class.id=student.class
WHERE student.class=:cid
group by class.title

using aggreation function and group you can show the aggregated result on the same row

answered Sep 03 '18 at 10:57

ScaisEdge

131,976
10
91
107

Thanks for your answer, is it possible to fetch expected result without using `GROUP_CONCAT`? I want to avoid `GROUP_CONCAT`. So I have used the word **without** `GROUP_CONCAT` in question. – Dipak Sep 03 '18 at 11:04
1

if you want the query result on the same rows you must use an aggregated function as group_concat and group by ... otherwise you can perform the same query you have used in your questiion and format the result using php .. building the string you need for output .. – ScaisEdge Sep 03 '18 at 11:06
anyway .. why you don't want group by and group concat ? .. do the fact you are selecting few rows filter by :cid .. and you have proper index of joi involved column you can't have performance problem.. – ScaisEdge Sep 03 '18 at 14:13
`GROUP_CONCAT()` invokes the group-by logic and creates temporary tables, which can be a big negative for performance. After reading this post - [https://stackoverflow.com/questions/26225069/reasons-not-to-use-group-concat], I am trying to avoid, if possible. – Dipak Sep 03 '18 at 14:22
1

well what 's your performance requirements ..? .. how many rows are involved by each table? ..how many rows in average are involved by each cid..? if you have billions of rows involved and for these billions of rows involved you need response for some decimal seconds then your performance your worries have sense .. otherwise .. not .. .. you should try executing the query .in a sql console or sql IDE and check for your real timing results ... you should read the answer in question but related to the real question .. and not simpy transpose the answer t – ScaisEdge Sep 03 '18 at 14:28

mysql join repeats row, order by caused problem

2 Answers2