Directory that lists employee information I'm working on a directory that lists employee information.  Each employee can belong to multiple departments and each employee can have multiple job titles.  I have 5 MySQL tables that I am pulling information out of, including: 


employee
employee_has_department
department 
employee_has_jobTitle
jobTitle


The employee table has a many-to-many relationship with department and jobTitle.  I currently have nested queries, as follows:

$stmt = $mysqli-&gt;prepare("SELECT employeeId, firstName, middleName, lastName, suffix, profilePhoto FROM employee");
$stmt-&gt;execute();
$stmt-&gt;store_result();
$stmt-&gt;bind_result($employeeId, $firstName, $middleName, $lastName, $suffix, $profilePhoto);
while($stmt-&gt;fetch()){

  echo "&lt;h4&gt;$firstName&lt;/h4&gt;";

  $stmt2 = $mysqli-&gt;prepare("SELECT jobName FROM jobTitle INNER JOIN employee_has_jobTitle ON jobTitleId = jobTitle_jobTitleId WHERE employee_employeeId = ?");    
  $stmt2-&gt;bind_param("i", $employeeId);
  $stmt2-&gt;execute();
  $stmt2-&gt;store_result();
  $stmt2-&gt;bind_result($jobName);
  while($stmt2-&gt;fetch()) {
    echo "$jobName&lt;br&gt;";   
  }

  $stmt3 = $mysqli-&gt;prepare("SELECT departmentName, departmentURL FROM department INNER JOIN employee_has_department ON departmentId = department_departmentId WHERE employee_employeeId = ?");    
  $stmt3-&gt;bind_param("i", $employeeId);
  $stmt3-&gt;execute();
  $stmt3-&gt;store_result();
  $stmt3-&gt;bind_result($deptName, $deptURL);
  while($stmt3-&gt;fetch()) {
      echo "&lt;a href='$deptURL'&gt;$deptName&lt;/a&gt;";
  }   
}


Clearly the output needs formatting, but it's getting the information and outputting as expected.  I know that this can probably be done in a single sql statement, but the complexity and ugliness of performing a join on 5 separate tables with multiple many-to-many relationships leaves me wondering.  Would taking the time to figure out such a query worth the hassle, or is this an "acceptable" way of handling the situation?
The answer is that there is a definite performance gain in having it all in a single join. Just the reduction in back and forth should greatly decrease the latency, and the database engine should be way more effective.

For a more general view on that topic :


Will the request be hard to understand later ? This is the most important question, as it will be the difference between maintainable code and code nobody will dare touch. In this particular case, I would say there is an obvious request to write, and that it will be fairly easy to understand. If you don't feel this is the case, then you probably should not go this way (premature optimization, etc.).
How complex will be the whole solution ? Displacing logic from PHP to SQL is often a good idea, but might have drawbacks :


hidden logic (triggers, or opaque stored procedures) that is hard to understand later
vendor lock-in (when using advanced features that are not standard).
poor performance : with complex queries, it becomes hard to evaluate the run-time performance


It seems to me that a straightforward 5 tables join is far from complex, and is a lot less ugly than the PHP code displayed here. You would get rid of all the content of the while loop ...
How much will the performance be impacted ? For a low-traffic web application, which is the most common case, this should be the last concern.