Selecting/Ordering by prerequisite?

[AngleWyrm]

So I have a table that has, among other things, these feilds:

id PRIMARY KEY
name VARCHAR(50)
prerequisite INT NOT NULL

Prerequisite contains a reference to the id of whatever item comes before
it.

How can I generate a query on this that will put them in sequence?

[Bill Karwin]

AngleWyrm wrote:

Prerequisite contains a reference to the id of whatever item comes before it.
How can I generate a query on this that will put them in sequence?

You haven't described what sequence you want them to be in.

Basically, you're describing tree-structured or heirarchical data, like
a parts-explosion, or threaded forum postings. You can show this
depth-first, breadth-first, or some other sequence.

Regards,
Bill K.

[AngleWyrm]

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:e7******** *@enews3.newsgu y.com...

AngleWyrm wrote:

Prerequisite contains a reference to the id of whatever item comes before
it.
How can I generate a query on this that will put them in sequence?

You haven't described what sequence you want them to be in.

An example set of records:

ID | Name | Prerequisite
1 |One |
2 |Two |1
3 |Three |2

In the example above, Three comes after Two, because it has a prerequisite
of item ID 2.

This gives a basic feel for the problem. And no, the ID isn't such an neatly
arranged numerical sequence.

[Bill Karwin]

AngleWyrm wrote:

ID | Name | Prerequisite
1 |One |
2 |Two |1
3 |Three |2

In the example above, Three comes after Two, because it has a prerequisite
of item ID 2.

But what about this case:

ID | Name | Prerequisite
1 |One |
2 |Two |1
3 |Three |2
4 |Four |1

What should the sequence be? "One, Two, Three, Four," or "One, Two,
Four, Three"? That's the difference between depth-first and
breadth-first, respectively.

Breadth-first is pretty easy:

SELECT *
FROM this_table
ORDER BY Prerequisite, ID

Depth-first is harder. You probably need to store the relationships
differently. You could implement the heirarchy using the nested-set
data model.
See http://dev.mysql.com/tech-resources/...ical-data.html

Regards,
Bill K.

[AngleWyrm]

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:e7******** @enews4.newsguy .com...

AngleWyrm wrote:

ID | Name | Prerequisite
1 |One |
2 |Two |1
3 |Three |2

In the example above, Three comes after Two, because it has a
prerequisite of item ID 2.

But what about this case:

ID | Name | Prerequisite
1 |One |
2 |Two |1
3 |Three |2
4 |Four |1

What should the sequence be? "One, Two, Three, Four," or "One, Two, Four,
Three"? That's the difference between depth-first and breadth-first,
respectively.

Breadth-first is pretty easy:

SELECT *
FROM this_table
ORDER BY Prerequisite, ID

Depth-first is harder. You probably need to store the relationships
differently. You could implement the heirarchy using the nested-set data
model.
See http://dev.mysql.com/tech-resources/...ical-data.html

There are no cyclical dependencies in the database that I am working with. I
do with it were up to me to alter the structure of the tables to use the
nested-set data model; thank you for the excellent pointer.

Perhaps I can automatically import the data into a private table, and
develop a left and right field for them in the process.

[Bill Karwin]

AngleWyrm wrote:

There are no cyclical dependencies in the database that I am working with.

I know what you mean, but for what it's worth, a tree is acyclic by
definition.

Regards,
Bill K.