title: MySQL fetch 2 related tables into sub array
body: <p>I have a MySQL database that contains the following tables:</p>
  
  <p><code>Student</code> table that looks like that:</p>
  
  <pre><code>|------------------------------------|
  | studentId | studentName | school   |
  |------------------------------------|
  | 1         | Student A   | 1        |
  | 2         | Student B   | 2        |
  | 3         | Student C   | 2        |
  |------------------------------------|
  </code></pre>
  
  <p>And a <code>School</code> table:</p>
  
  <pre><code>|------------------------------|
  | schoolId | schoolName | desc |
  |------------------------------|
  | 1        | School A   | ...  |
  | 2        | School B   | ...  |
  | 3        | School C   | ...  |
  |------------------------------|
  </code></pre>
  
  <p>Using PHP, I'm trying to fetch an array of all students and include their school information in a sub array. </p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
          (
              [studentId] =&gt; 1
              [studentName] =&gt; Student A
              [school] =&gt; Array
                  (
                      [schoolId] =&gt; 1
                      [schoolName] =&gt; School A
                      [desc] =&gt; ...
                  )
          )
      [1] =&gt; Array
          (
              [studentId] =&gt; 1
              [studentName] =&gt; Student A
              [school] =&gt; Array
                  (
                      [schoolId] =&gt; 1
                      [schoolName] =&gt; School A
                      [desc] =&gt; ...
                  )
          )
      [2] =&gt; Array
          (
              [studentId] =&gt; 1
              [studentName] =&gt; Student A
              [school] =&gt; Array
                  (
                      [schoolId] =&gt; 1
                      [schoolName] =&gt; School A
                      [desc] =&gt; ...
                  )
          )
  )
  </code></pre>
  
  <p>I was able to achieve that by doing this</p>
  
  <pre><code>$studentsResult = $conn-&gt;query("SELECT * FROM STUDENT");
  $studentsArray = $studentsResult-&gt;fetch_all(MYSQLI_ASSOC);
  
  $finalArray = array();
  
  foreach ($studentsArray as &amp;$student) { 
      $schoolSQL = "SELECT * FROM SCHOOL WHERE schoolId = ".$student['schoolId'];
      $schoolResult = $conn-&gt;query($schoolSQL);
      $schoolArray = $schoolResult-&gt;fetch_all(MYSQLI_ASSOC);
  
      unset($student['schoolId']);
      $student['school'] = $schoolArray[0];
      $finalArray[] = $student;
  }
  echo '&lt;pre&gt;'; print_r($finalArray);
  </code></pre>
  
  <p>But I don't think this way is the most efficient for a large database since I'm looping through all the students and executing a query to get school row.</p>
  
  <p>Is there anyway I can do all that by using only SQL?</p>