title: MySQL Multiple Join Query with Limit on One Join
body: <p>I have a <strong>MYSQL</strong> query I'm working on that pulls data from multiple joins. </p>
  
  <pre><code>select students.studentID, students.firstName, students.lastName, userAccounts.userID, userstudentrelationship.userID, userstudentrelationship.studentID, userAccounts.getTexts, reports.pupID, contacts.pfirstName, contacts.plastName, reports.timestamp
  
   from userstudentrelationship  
  
  join userAccounts on (userstudentrelationship.userID = userAccounts.userID)
  join students on (userstudentrelationship.studentID = students.studentID) 
  join reports on (students.studentID = reports.studentID) 
  join contacts on (reports.pupID = contacts.pupID) 
  
  where userstudentrelationship.studentID = "10000005" AND userAccounts.getTexts = 1 ORDER BY reports.timestamp DESC LIMIT 1
  </code></pre>
  
  <p>I have a unique situation where I would like one of the joins (the reports join)  to be limited to the latest result only for that table (order by reports.timestamp desc limit 1 is what I use), while not limiting the result quantities for the overall query. </p>
  
  <p>By running the above query I get the data I would expect, but only one record when it should return several. </p>
  
  <p>My question: </p>
  
  <p>How can I modify this query to ensure that I receive all possible records available, while ensuring that only the latest record from the reports join used? I expect that each record will possibly contain different data from the other joins, but all records returned by this query will share the same report record</p>