Recursive-type select help required

Recursive-type select help required

Post by Vertigen, Charles » Thu, 15 Jul 1999 04:00:00



In a table structured as follows:

        family          integer not null, - PRIMARY KEY
        message integer not null,
            parent              integer not null

        This structure behaves like a linked list, each message created has
a parent of the last message created within the         family.

        So, the question is:

        given message 103 what message has zero parent within the same
family?

        family  message    parent
             1  100                 0   (first message for family has zero
parent)
             1  101                     100
             1  102                     101
             1  103                 102

        The SQL cannot use family as this is not indexed and the parents or
messages are not necessarily in any order.

        Any replies would be appreciated!

        (I seem to remember Pat McGibbon asking something similar to this a
while back if I'm not mistaken.

Yours,

Charles Vertigen

 
 
 

Recursive-type select help required

Post by Chris Simo » Sat, 17 Jul 1999 04:00:00


Quote:>         given message 103 what message has zero parent within the same
> family?

>         family  message    parent
>              1  100                 0   (first message for family has zero
> parent)
>              1  101                     100
>              1  102                     101
>              1  103                 102

SELECT message FROM table WHERE parent = 0
   AND Family IN (SELECT Family FROM table WHERE message = 103)

You mentioned that Family is not indexed, but I don't see how that would
affect the above solution.

--
Chris Simon,
Analyst/Programmer,
Prifysgol Cymru Bangor - University of Wales Bangor,
01248 382417

 
 
 

1. help,help,help,help,help,help,help,help,help,help,help,help,help,

Hi,
I have a small problem, and if you know the answer I would really be
thankful for your help.
I have an access database which is called "Client.mdb"
on a form, I displayed a dbcombox for the costumer's name
a textbox for the address
a textbox for the city
a textbox for the state
and a textbox for the zipcode

on all my textboxes and the dbcombobox I have set their Datasource and
Datafield properties to Data1,
when I run the program the information on the first record of the data shows
on the form,

but what I need to do is, when I change the costumer's name on the
dbcombobox (which displays all of the costumer's names on it) I want the
textboxes to show the address, city, state, and zipcode of the costumer I
have choosen,

Thank you in advance,
Enrique G Lugo

2. DSO and Cube.LastModified

3. Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,

4. tokens question (newbie question) --how :remember fiels from a search_result page so i can insert them later in a add-record-field

5. SQL To ORACLE Help Help Help Help Help

6. How to print BarCode from VB5 ?????

7. query:HELP! HELP HELP HELP HELP

8. Evidence of MicroSoft Plundering Sybase Code(still)

9. Select TOP x FROM HELP HELP HELP!

10. HELP HELP HELP HELP!

11. HELP HELP HELP HELP

12. Problems doing selects accross Linked Servers - Help Required Urgently

13. Need help selecting prog type