title: group by and group_concat alternative
body: <p><em>Using <code>GROUP_CONCAT()</code> usually invokes the group-by logic and creates temporary tables, which are usually a big negative for performance. Sometimes you can add the right index to avoid the temp table in a group-by query, but not in every case.</em></p>
  
  <p><a href="https://stackoverflow.com/a/26225148/9685125">https://stackoverflow.com/a/26225148/9685125</a></p>
  
  <p>After Reading this post, I realized that I was doing wrong, because many time I made complicated query using huge <code>GROUP_CONCAT()</code>. Such as</p>
  
  <pre><code>GROUP_CONCAT(DISTINCT exam.title) AS exam,
  GROUP_CONCAT(subject.title, '&lt;br/&gt; Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark
  </code></pre>
  
  <p>But what can be alternative of <code>GROUP_CONCAT</code> in following situation without using <code>subquery</code>. I mean using only <code>Mysql join</code>, </p>
  
  <p>For example, let see two relational database and and query to explain my problem</p>
  
  <p><strong>Student</strong></p>
  
  <pre><code>id  |   Rid |   name
  ========================
  1   |   1   |   john
  </code></pre>
  
  <p><strong>Marks</strong></p>
  
  <pre><code>id  |   std_id  |   th
  ======================
  1   |   1       |   60
  2   |   1       |   70
  3   |   1       |   80
  4   |   1       |   90
  
  "SELECT 
      student.en_name, mark.th 
      FROM student
      JOIN mark ON student.id = mark.std_id
      WHERE student.id=:id;"
  </code></pre>
  
  <p>Column would be repeated if only use <code>JOIN</code></p>
  
  <pre><code>John: 60, John: 70, John: 80, John: 90
  </code></pre>
  
  <p>So, I use <code>GROUP BY</code>. But if I assign <code>GROUP BY</code> to student.id, only first row is fetched</p>
  
  <pre><code>"SELECT 
      student.en_name, mark.th 
      FROM student
      JOIN mark ON student.id = mark.std_id
      WHERE student.id=:id
      GROUP BY student.id;"
  
  Result is
  John: 60
  </code></pre>
  
  <p>So to get result, we have to assign that <code>group.concat</code></p>
  
  <pre><code>"SELECT 
      student.en_name,
      GROUP_CONCAT(mark.th) as mark 
      FROM student
      JOIN mark ON student.id = mark.std_id
      WHERE student.id=:id
      GROUP BY student.id;"
  </code></pre>
  
  <p>And final and expected result using exploding array</p>
  
  <pre><code>$name=$row['en_name'];
  echo "$name: &lt;br/&gt;";
  $mrk_array = explode(',',$row['mark']); 
  foreach($mrk_array as $mark){
      echo $mark.", ";
  }
  
  John:
  60, 70, 80, 90,
  </code></pre>
  
  <p>Here, I don't see any alternative of <code>GROUP_CONCAT</code> to fetch all associated value of each <code>Id</code> and prevent duplicate, please help me how to replace <code>GROUP_CONCAT</code> from here.</p>
  
  <p>Also, one friend told me</p>
  
  <p><strong>So why <code>GROUP_CONCAT</code> if you're "exploding" it. You might as well return a nice associative array and then deal with displaying it there.</strong> </p>
  
  <p>But I can't understand, what he means ?</p>