Re: SQL, related records (quotes)
From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Sat, 25 Jun 2005 00:43:21 +0200
Message-ID: <42bc8c8b$0$42378$e4fe514c_at_news.xs4all.nl>
>>>> mAsterdam wrote:
>>>> 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?
Date: Sat, 25 Jun 2005 00:43:21 +0200
Message-ID: <42bc8c8b$0$42378$e4fe514c_at_news.xs4all.nl>
Stefan Rybacki wrote:
> mAsterdam wrote: >> mAsterdam wrote: >>> 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? >>>>>>> >>>>>>> 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?
>>> >>> Consider adding these constraints: QuoteNo as key, >>> and PreviousQuoteNo as foreign key to QuoteNo. >>> >>> 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. >> >> Oops - still not good enough. > > It should be good enough for an insert but not for an update, or did I > forget something?
Exactly. Now in this specific situation a check constraint, PreviousQuoteNo < QuoteNo, would do the trick (I hope am not still forgetting something).
Is there a more generic way to get the wanted tree-garantuee? Sigh. No escape - I'll just have to buy Celko's book :-) Received on Sat Jun 25 2005 - 00:43:21 CEST