Re: SQL, related records (quotes)
From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Sat, 25 Jun 2005 00:16:16 +0200
Message-ID: <42bc8632$0$38881$e4fe514c_at_news.xs4all.nl>
>
> That is true but these are inconsitent states. Its clear that you can
> create those table values by hand but think about it in algorithmic way:
>
> you want to create a quote with QuoteNo=3 so how can you say its parent
> is QuoteNo=6 since there is no quote with this number. Ok somebody can
> say what if QuoteNo=6 is already existing. Ok we go from there so how
> could this quote (6) have known from QuoteNo=3? since we are currently
> creating it?
>
> Same for the second example.
Date: Sat, 25 Jun 2005 00:16:16 +0200
Message-ID: <42bc8632$0$38881$e4fe514c_at_news.xs4all.nl>
Stefan Rybacki wrote:
> mAsterdam wrote:
>> Jan Hidders wrote: >>> Stefan Rybacki wrote: >>>> timpea_at_gmail.com wrote: >>>>> 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? >>>>> Thanks Tim Pearson >>>> >>>> Try the nested sets model. >>> >>> Are you sure the relation describes always a tree? >> >> Values like >> QuoteNo PreviousQuoteNo >> 3 6 >> 6 3 >> >> or just >> QuoteNo PreviousQuoteNo >> 3 3 >> >> are possible, so no tree. The columnnames suggest the OP does not >> want this to happen. They don't help to prevent these loops though.
>
> That is true but these are inconsitent states. Its clear that you can
> create those table values by hand but think about it in algorithmic way:
>
> you want to create a quote with QuoteNo=3 so how can you say its parent
> is QuoteNo=6 since there is no quote with this number. Ok somebody can
> say what if QuoteNo=6 is already existing. Ok we go from there so how
> could this quote (6) have known from QuoteNo=3? since we are currently
> creating it?
>
> Same for the second example.
Consider adding these constraints: quote as key, and previousqoute as foreign key to quote.
Only now we have the intended tree-consistency. The circular states mentioned above are inconsistent with the constraints, and I can't add these values by hand or by algorithm. Received on Sat Jun 25 2005 - 00:16:16 CEST