Paged results when selecting data from 2 databases

Hi

I have one web service connected to one db that has a table called clients which has some data.

I have another web service connected to another db that has a table called clientdetails which has some other data.

I have to return a paged list of clients and every client object contains the information from both tables.

But I have a problem.

The search criteria has to be applied on both tables.

So basically in the clients table I can have the properties:

cprop1, cprop2

in the clientdetails table I can have cdprop1,cdprop2

and my search criteria can be cporp1=something, cdprop2 = somethingelse

I call the first web service and send it the criteria cporp1=something

And it returns some info and then I call the method in the second web service but if I have to return say 10 items on a page and the criteria of the second web service are applied on the 10 items selected by the first web service(cdprop2 = somethingelse) then I may be left with 8 items or none at all.

So what do I do in this case?

How can I make sure I always get the right number of items(that is as much a开发者_C百科s the user says he wants on a page)?

---

Until you have both responses you don't know how many records you are going to have to display.

You don't what kind of database access you are using, you imply that you ask for "N records matching criterion X", where you have N set to 10. In some DB access mechanisms you can ask for all matching records and then advance a "cursor" through the set, hence you don't need to set any upper bound - we assume that the DB takes care of managing resources efficiently for such a query.

If you can't do that, then you need to be able to revisit the first database asking for the next 10 records, repeat until finally you have a page full or no more records can be found. This requires that you have some way to specify a query for "next 10".

You need the ability to get to all records matching the criteria in some efficient way, either by some cursor mechanism offered by your DB or by your own "paged" queries, without that capability I don't see a way to guarantee to give an accurate result.

---

I found that in instances like this it's better not to use identity primary keys but primary keys with generated values in the second database(generated in the first database).
As for searching you should search for the first 1000 items that fit your criteria from the first database, intersect them with the first 1000 that match the given criteria from the second database and return the needed amount of items from this intersection.
Your queries should never return an unlimited amount of items any way so 1000 should do. The number could be bigger or smaller of course.