Selecting/Ordering by prerequisite?

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?

Re: Selecting/Ordering by prerequisite?

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.

Re: Selecting/Ordering by prerequisite?

"Bill Karwin" wrote in message
news:e77akl01vog@enews3.newsguy.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.

Re: Selecting/Ordering by prerequisite?

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/articles/hierarchical-da ta.html

Regards,
Bill K.

Re: Selecting/Ordering by prerequisite?

"Bill Karwin" wrote in message
news:e7et4a0n74@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/articles/hierarchical-da ta.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.

Re: Selecting/Ordering by prerequisite?

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.