如何使用join语句

Question

How can i do this with JOIN statement. I can't think of anymore logic to do this. I'm doing this because the json data is not shown because of that queries. Can someone help me to achieve this? First i want to fetched all the enroll_ref from subj_enrolled WHERE subject is equals to '$subject'. After that, I will now use the fetched enroll_ref to fetch the lastname,firstname,middlename to the std_enrolled where equals to enroll_ref. What method will i use to achieve this? Or what JOIN?

here's my code

if (isset($_POST['loadstudent'])) {
    $subject = $_POST['subject'];
        $section = $_POST['section'];

    $sql ="SELECT enroll_ref FROM subj_enrolled WHERE subj_descr = '$subject' AND section = '$section'";
           $result = mysqli_query($con, $sql);

           while($row = mysqli_fetch_array($result)){
            $enroll_ref = $row['enroll_ref'];


      $select = mysqli_query($con,"SELECT lastname,firstname,middlename FROM std_enrolled WHERE enroll_ref = '$enroll_ref'"); 

      if(mysqli_num_rows($select)){
            $data = array();
            while($row = mysqli_fetch_array($select)){
                  $data[] = array(
                        'firstname' => $row['firstname'],
                        'lastname' => $row['lastname'],
                         'middlename' => $row['middlename']

                        );
            }

          }
                       echo json_encode($data);
           }


}

Answer 1

Use an inner join.

Inner joins return all data where there is a match between the two tables.