Re: SQL, related records (quotes)

<timpea_at_gmail.com> wrote in message
news:1119628561.458253.110450_at_g44g2000cwa.googlegroups.com...
> Hi,
>
> I have a website that users can request quotes, and a user may also
> make a new quote that links to a previous quote.
>
> QuoteNo PreviousQuoteNo
> 1
> 2
> 3 1
> 4 3
> 5 4
>
> If i request quoteNo 3 i want to have a list of all the related quotes.
>
>
> RelQuote
> 1
> 3
> 4
> 5
>
> Is this possible with a SQL statement? or would i be best doing a loop
> in asp and many requests?

You could model this as a binary equivalence relation and merge it with your original "table". Since you are interested in relationships that are reflexive, symmetric, and transitive in nature (in contrast to directed one-way relationships), your biggest design decisions would be: 1. Deciding what symbolic notation or surrogate value to use to identify the distinct equivalence classes.
2. Providing under the covers or explicit application logic to automate assignment of equivalence identity correctly for inserts, deletes, and updates.

There is more complexity for updates with this solution, but far less for queries, which would scale much better than most other ways of calculating closure "on the fly" with large chains or families.

So, using your previous representation (I might have done some things a little bit differently) for example:

QuoteNo       PreviousQuoteNo      Quote_Chain
1                         ..Null..                     1
2                         ..Null..                     2
3                             1                         1
4                             3                         1
5                             4                         1

As you can see, the relation accounts for groupings of related quotes as a quote chain or quote family. In this case, I decided to use the arbitrary rule of taking the identifier with the least numeric value within a given chain as the identifer for the grouping. The trade-off is the complexity in recalculating closure when updates such as value changes, reassignment, or deletions occur. If these occurances will generally be rare, this is a pretty fast way to query closure of equivalence.

To get all related quotes to quote #3, the query would be:

SELECT QuoteNo
  FROM quotes quotes1
  WHERE EXISTS (SELECT 'x'

                                  FROM quotes quotes2
                                  WHERE QuoteNo = 3
                                      and quotes2.Quote_Chain = 

>
> Thanks
> Tim Pearson
>
Regards,

Daniel Guntermann Received on Sun Jun 26 2005 - 00:15:50 CEST