Search On Table With Recursive Relationship

I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.

1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?

2. I need to be able to do a search in this table. Example:

Table: tbComponent
Columns: Co_ID Integer
Co_Co_ID Integer
Co_Name Text
Co_Attribute Text
Co_Category Text


Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.

A typical group of records could be something like this:


Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category

1 1 Car Blue Ford
2 1 Body Steel Parts
3 2 Door Front Parts
4 3 Invoice April 1 1999 Accounting

Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:

If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.

Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.

Thanks for any and all input!