Thanks Jim, that got me on the right track. This is related to a article by Celco, posted by someone on this list, relating to managing tree relationships with sql. It would apply to organizational charts, geneology, MLM organizations, etc... (search celco at intellegententerprise.com)
In my case I keep inching it along with the intent of tracking inventory usage by sub-assemblies for a manufacturing client. What makes this essentially different than other approaches is that you can eliminate any one element and the remaining relationships are preserved. Ben Petersen On 28 Dec 2001, at 9:03, Jim Blackburn wrote: > Ben: > It looks to me like your solution will not work. The condition below eliminats ID2 >from consideration: > and (t2.A+t2.B) < (t1.A+t1.B) and t1.ID = (.zID) > The term (A + B) is 13, 15, and 15 on the three records. > > What looks to me to be a solution is to recognize that knowing the highest A that is >less than the A in ID3 is all that is needed: > Assuming structure: Table (ID text, A int, B int) > > set var vID text = 'ID3' > > select ID, A, B from Table WHERE A = (select max(A) from Table WHERE A < (select A >from Table WHERE ID = .vID)) > > Jim Blackburn > Kodiak > > > Ben Petersen wrote: > > > > On 27 Dec 2001, at 15:21, Gary Winzeler wrote: > > > Whats the two steps? > > 1) > > Assuming "Table" looks like this > > ID, A, B > > ------------ > > ID1,1,12 > > ID2,4,11 > > ID3,7,8 > > > > Set v vID Text = 'ID1' > > > > Create temp view Test (ID, A, B, AB) as + > > sel t2.ID, t2.A, t2.B, (t2.A+t2.B) + > > from Table t1, Table t2 + > > group by t2.ID, t2.A, t2.B + > > whe t1.A between t2.A and t2.B and (t2.A+t2.B) < (t1.A+t1.B) and > > t1.ID = (.zID) > > > > This is probably a bit much just for the three records in my > > example, but in reality there could be many groupings like this in > > various sizes. This view extracts all records two numerics would > > enclose record being queried. > > > > 2) > > sel ID from Test whe AB = (sel max(AB) from Test) > > ---------------------------- > > >From the few samples I've tested, this logic holds up, but I'm not > > certain yet. This just seems a bit convoluted, and was looking for a > > cleaner solution. > > > > Thanks, > > > > Ben > > > > > > > > At 03:07 PM 12/27/2001 +0000, you wrote: > > > > > > >Hi all. Looking at the snippit of data records below, how would a > > > >person do a select that, using ID3 data, would return ID2 (and not > > > >ID1)? > > > > > > > >Working from the bottom up, you can count on the two numerics of > > > >each record being bounded by the next record, ie 4 and 11 of ID2 > > > >would enclose 7 and 8 of ID3... 1 and 12 of ID1 enclose 4 and 11 of > > > >ID2. > > > > > > > >So, if you only knew the values of ID3, how would you construct a > > > >select that only returned ID2? I can do it in two steps (I think), but > > > >would rather one. > > > > > > > >ID1,1,12 > > > >ID2,4,11 > > > >ID3,7,8 > > > > > > > >tia, > > > > > > > >Ben Petersen > > > >================================================ > > > >TO SEE MESSAGE POSTING GUIDELINES: > > > >Send a plain text email to [EMAIL PROTECTED] > > > >In the message body, put just two words: INTRO rbase-l > > > >================================================ > > > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > >In the message body, put just two words: UNSUBSCRIBE rbase-l > > > > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: INTRO rbase-l > > > ================================================ > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l
