Full-Text Search for category with all parents in a row

Question

Full-Text Search for category with all parents in a row: CatLevel1 >> CatL2 >> CatLn >> SearchedCategory

I've got 4 columns in my category table: CatID, CatName, CatDepth, ParentID Some categories got a 3 level depth and some other 7. I want to have the target category with all its parents in one row.

Let me explain by an example:

Searched Keyword: metal Search Result: 1: Finished Machinery > Machine Tools, Wood & Metalworking Machinery > Metal Processing Machinery > Metal Bending Machinery 2: Non-Metallic Minerals > Other Products > Metal Processing Machinery > Metal Bending Machinery 3: Non-Metallic Minerals > Other Products > Metal Processing Machinery > Metal Casting Machinery ...

I tried many ways to write a flexible query, but none of them work as I wish. Any kind help would be highly appreciated. Thanks Kardo

[ekardo]-[at]-[gmail]-[dot]-[com]

Answer 1

If this is a hierarchic Bill of Materials, then you could use

CONNECT BY 

to return the hierarchy (in Oracle)