Recursive / Heirarchical query?

[AngleWyrm]

I have two tables, one with data and the other specifying relationships:
Technologies (TechID, TechName)
Prerequisites(TechID, PrerequisiteID)
In the Prerequisites table, the PrerequisiteID refers to another entry in the Technologies table. There may be zero or more prerequisites entries for each technologies entry.

What I want to do is query the database so that I get a results table listing all the prerequisites for a given TechID.

The data in Prerequisites is guaranteed not to be cyclical, it is like a tree structure.

How to get started?

[Rawhide]

Assuming you are given a starting TechID (such as a user selects one from a drop down select box)

Select *
From Prerequisites As P
Left Join Technologies As T On T.TechID = P.PrerequisiteID
Where P.TechID = User submitted TechID


Or to return all possibilities:

Select *
From Technologies As T1
Left Join Prerequisites As P On P.TechID = T1.TechID
Left Join Technologies As T2 On T2.TechID = P.PrerequisiteID
Order By T1.TechID, T2.TechID

[AngleWyrm]

Trying with MS Access:

SELECT *
FROM
(
Technologies AS T1
LEFT JOIN Prerequisites AS P
ON P.ID =T1.ID
)
LEFT JOIN Technologies AS T2
ON T2.ID = P.ID
ORDER BY T1.ID, T2.ID;

The resulting record set is the same size as the prerequisites record set.

What I'm trying to get is a list of all the ancestors of a given technology, not just the immediate parents of a given technology.

I know I'm doing it wrong, how should it be done?

[skhanal]

Oracle has CONNECT TO clause that allows you to list hierarchical relations. SQL 2000 does not have anything equivalent (it will be there in SQL 2005). However SQL Server books online has an example stored procedure doing this.