Hi, 
We would like the SQL for the following:
We have a data set in two tables in MySQL on PHP.
Table1 contains data, table2 contains relationships between items in table1

Table1
Entry_id
Notes

Table2
PrimaryKey
Up (contains a Table1.entry_id)
Down (contains a Table1.entry_id)

This is the data:

             1                        2
       3   4     5                    6
     7  8      9   10
     

The numbers refer to entry_id's of notes in table1.  The positions
refer to the relative organization in table2.

We would like the data to display in the following order:
1
3
7
8
4
5
9
10
2
6


If you need sample data or more detail I can provide.

---

Clarification of Question by universityresource-ga on 03 Nov 2004 05:11 PST

Also,
If we could associate a new field called "Level" with each item that
would represent its level, for instance:
Entry_id 1 and 2 would have a Level=1
Entry_id 3,4,5 and 6 would have a level=2
Entry_id 7,8,9 and 10 would have a level=3
and so on..

Thank you.

---

Clarification of Question by universityresource-ga on 03 Nov 2004 05:51 PST

Sample Data:
Table1
Entry_id, note
    1 note
    2 note
    3 note
    4 note
    5 note
    6 note
    7 note
    8 note
    9 note
   10 note

Table2
Key   Up Down
  1    1  3
  2    1  4
  3    1  5
  4    2  6
  5    3  7
  6    3  8
  7    5  9
  8    5 10

---

Request for Question Clarification by mathtalk-ga on 03 Nov 2004 11:20 PST

Hi, universityresource-ga:

What version of MySQL are you using?  

regards, mathtalk-ga

---

Clarification of Question by universityresource-ga on 03 Nov 2004 16:45 PST

MySQL version 4.0
PHP version 4.3

---

Clarification of Question by universityresource-ga on 04 Nov 2004 04:51 PST

Great,
We would like the PHP.
include(connection.php)

Unfortunately recursion, which is implicit in finding depth first
search order here, is not a strong point of SQL.  One would need to
emulate recursion through some iteration, which implies procedural
programming.

SQL is primarily a declarative programming language, largely based on
first-order logic.  However many SQL implementations, including MySQL
starting with version 5.0, allow for a variety of procedural
constructs including iteration (looping) within the framework of
stored procedures (or in scripts, for some implementations).

With MySQL 4.0 my guess would be that some reliance on PHP code
calling into the database repeatedly is the cleanest approach.

regards, mathtalk-ga