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

Do you have to bring back all of the orders for the 100 customers? Or could
you, instead, bring back the most recent order for each of the 100 customers
and then provide drilldown funcationality to see other associated orders?


"Bob" <boblotz2001 (AT) yahoo (DOT) com> wrote

Actually I have to bring all the orders for all the customers. That's
the root of the problem.

Thanks

Bob

"Adam Machanic" <amachanic (AT) hotmail (DOT) _removetoemail_.com> wrote

Bob wrote:
then what does the "TOP 100" have to do with it? Sounds more like an ORDER BY
issue. Note that you can order by information not dosplayed in the SELECT.


--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost