Hi there,

Here is another SQL related problem.  I have an application that
performs frequent searches.  One of the requirements is that it should
bring back an exact number of entities being searched no matter how
many describing rows there could be.  So for example: say you are
searching for customers and displaying customer orders.  Since
customers could have many orders doing SELECT TOP 100 would return not
100 customers but one hundred orders.  I see numerous alternatives
here: one, perform one SELECT that returns customers and then perform
100 SELECTs to return order for each customer.  Two, include a join in
the search SQL to a subquery that looks something like this:

SELECT custmers.name, orders.number
FROM customers join orders on customers.customer_id =
orders.customer_id
JOIN (select top 100 customer_id FORM customers WHERE customer.name =
'smith') AS top_customers ON customers.customer_id =
top_costumers.costumer_id

This approach presents its own problems like the redundant joins,
longer sql not to mention others.

Any thoughts?

Thanks

Bob