Database query while looping

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.

I have a table called People, then a table called Jobs that stores all of their employment history.

It comes 开发者_运维问答up fairly often when creating reports, dashboards, etc. where we want to list each person and their last 3 jobs (for example):

John Smith

• Some Company, 4/3/2011-5/14/2011
• Another Company, 3/12/2010-4/1/2011
• Different Company, 8/1/2009-1/4/2010

Sally Smithers

• Some Company, 4/3/2011-5/14/2011
• Another Company, 3/12/2010-4/1/2011
• Different Company, 8/1/2009-1/4/2010

Etc.

Some pseudocode that's VBish:

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

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.

Or if someone can point me to this question asked previously, that would be good too.

Thanks.

Edit: 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.

Assuming SQL Server 2005 or higher:

SELECT  *
FROM    people p
OUTER APPLY
        (
        SELECT  TOP 3 *
        FROM    job j
        WHERE   j.personId = p.id
        ORDER BY
                j.applicationDate DESC
        ) j

or this:

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY j.appicationDate DESC) AS rn
        FROM    people p
        LEFT JOIN
                job j
        ON      j.personId = p.id
        ) q
WHERE   rn <= 3

I suggest that you do the following and it will be 2 database calls only

SELECT PersonID, Name FROM People
SELECT PersonID, JobID, CompanyName, OtherFields FROM Jobs

Do While datareaderPeople.Read()
   Response.write(datareaderPeople("Name")
   Filter Jobs records data with DataView using RowFilter = "PersonID = " + datareaderPeople("PersonID")

   Do While FilteredRows.Read()
       Response.write(FilteredRows("CompanyName"))
   End While
End While