Query to retrieve hierarchy

[apollo11]

I have one table (category) which stores information about category items with their direct parent i.e till one level
something like:
cat_id, name, pcat_id

where cat_is is the primary key and Name is the description and pcat_id is the cat_id of the parent ( for top level categories value is null)
For instance the foll. records:
cat_id name pcat_id
1 School Supplies null
2 Writing material 1
3 Pencils 2
4 Pens 2
5 Pencil Erasers 2

and so on and so forth
I want to run a query that does a contains text search on all subcategories and also returns me the name(s) of all the corresponding top level categories irrespective of the fact that the top level contains the text or not.
For instance search on "pen" whould return:

3 Pencils 2
4 Pens 2
5 Pencil Erasers 2
2 Writing material 1
I know you can do this with a query like
select cat_id, name from category where name like 'pen' or cat_id in (select pcat_id, name from category where name like 'pen')
but this takes a long time.

Is there any other way to get this result set without making the use of views, stored procs (temp tables) etc.
I know there is a way to create a 2 way table join with the same table aliased seperately but I can't get it to work.
Can anybody help me with this? Thanks

 

[Chao]

Try this,

select *
from Category
start with cat_id in (
select distinct cat_id
from category
where upper(name) like 'PEN%')
connect by prior pcat_id = cat_id;

Hope it is not too late.

 

[Chao]

Sorry, the above solution is for Oracle. I thought I was in Oracle forum.

 

[cheyney]

I don't know if this would be any faster, but you can try it:

select cat_id, name
from category
where name like 'pen'
UNION
select p.cat_id, p.name
from category c INNER JOIN category p ON c.pcat_id = p.cat_id
where c.name like 'pen'



Cheyney