Posted by Hugo Kornelis on 03/23/07 22:52

On 22 Mar 2007 15:59:59 -0700, yin_n_yang74@yahoo.com wrote:

>When I create the stored procedure, it joins multiple one-to-many
>relationship tables. This results in repeated/duplicate records. Is
>this an issue that should be solved within the stored procedure, or is
>this inevitable? If latter, how do you eliminate the duplicates in
>Crystal Reports?

Hi yin_n_yang74,

You are trying to pair basically unrelated things (yes, both equipment
and food are related to an event, but there is no relationship between a
particular food row for an event and a particular equipment row for the
same event). That's why the join causes the information to be
duplicated. More aboout this problem, and a possible solution if you
want to solve this server-side, is on my blog:
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/14/relating-unrelated-rows.aspx

However, the best solution is to handle this client side. I must admit
that I know nothing about Crystal Reports. But IF CR can handle it, I'd
advise you to open two seperate rowsets at once (one for event and food
information, using a basic joined query and ordering by event, and one
for the equipment information, also odered by event). Then process row
from both recordsets in a balanced line fashion: read one row from each
set and generate an output line until one set changes event; at that
point deplete the other set until it too changes event. Repeat until
both sets are exhausted.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]