title: Database query while looping
body: <p>I'm 99% sure this question has been asked 100 times, but I can't find anything on SO about the best way to do this.</p>
  
  <p>I have a table called People, then a table called Jobs that stores all of their employment history.</p>
  
  <p>It comes up fairly often when creating reports, dashboards, etc. where we want to list each person and their last 3 jobs (for example):</p>
  
  <p>John Smith</p>
  
  <ul>
  <li>Some Company, 4/3/2011-5/14/2011</li>
  <li>Another Company, 3/12/2010-4/1/2011</li>
  <li>Different Company, 8/1/2009-1/4/2010</li>
  </ul>
  
  <p>Sally Smithers</p>
  
  <ul>
  <li>Some Company, 4/3/2011-5/14/2011</li>
  <li>Another Company, 3/12/2010-4/1/2011</li>
  <li>Different Company, 8/1/2009-1/4/2010</li>
  </ul>
  
  <p>Etc.</p>
  
  <p>Some pseudocode that's VBish:</p>
  
  <pre><code>SELECT PersonID, Name FROM People
  
  Do While datareaderPeople.Read()
     Response.write(datareaderPeople("Name")
     'SELECT TOP 3 PersonID, JobID, CompanyName, OtherFields FROM Jobs WHERE PersonID = datareaderPeople("PersonID") ORDER BY SomeDateField
     Do While datareaderJobs.Read()
         Response.write(datareaderJobs("CompanyName"))
     End While
  End While
  </code></pre>
  
  <p>As you can see we're currently doing another query to get the Jobs for each person as we loop through the people.  Is there a better way to do this?  This way seems inefficient and creates lots of db queries.</p>
  
  <p>Or if someone can point me to this question asked previously, that would be good too.</p>
  
  <p>Thanks.</p>
  
  <p><strong>Edit:</strong>  I'm using the method above because I need to be able to do things with the Jobs fields for each Jobs row I get back.  Like maybe format the date, bold the company name,  etc.  Just getting back 1 big row with the Jobs fields combined into 1 big string wouldn't work.</p>