Database query while looping

Question

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.

You may perform a join on the tables, this is very inefficient as you may imagine (1000 people result means 1001 SQL queries). — Can Poyrazoğlu
– Can Poyrazoğlu, Commented Jul 20, 2011 at 16:24
is jobID an incremental for each person. so, the key for Jobs is PersonID, JobID? If this is the case, you can use a simple join and avoid Quassnoi's subqueries. EDIT: maybe I'm wrong, selecting off of top 3 id's would be hard.... getting the first 3 would be easy. — user606723
– user606723, Commented Jul 20, 2011 at 16:33
@can poyrazoğlu - Doing a join would just return 1 row for each person, no? I need to be able to do stuff with the individual fields in the Jobs rows. (see question edit) Thanks. — user714241
– user714241, Commented Jul 20, 2011 at 20:43

Quassnoi · Accepted Answer · 2011-07-20 16:24:38Z

2

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

answered Jul 20, 2011 at 16:24

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user714241 Over a year ago

So what would these mean for my application code? For example if I'm using vb.net or c#, how would one loop through the returned rows? The main reason I use the method in the question is because I want to be able to do stuff with the fields in the Jobs rows. Maybe format a date field, bold the Company Name text, etc. Wouldn't these methods just return 1 row for each person and not give me access to the individual fields in the Jobs rows? Thanks.

Samir Adel · Accepted Answer · 2011-07-20 16:45:14Z

1

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

answered Jul 20, 2011 at 16:45

Samir Adel

2,5092 gold badges15 silver badges16 bronze badges

2 Comments

user714241 Over a year ago

The general sentiment toward filtering rows in the application seems to be don't do it. Is this a case where using filtering rows is the better option because it would prevent making, potentially, hundreds of database calls for 1 page load?

Samir Adel Over a year ago

Sorry, i didn't get the meaning of your question

Collectives™ on Stack Overflow

Database query while looping

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related