>> I have table structure like
cat_id, cat_name, cat_parent_id <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

Next, please read and learn ISO-11179 data element naming conventions. There is no such thing as a "cat_id"; an attribute can be an identifier or a category, but never both. Think about it.

Finally, get a copy of TREES & HIERARCHIES IN SQL and/or Google "nested sets model" for a very easy, very fast solution. You are still thinking that DDL and DML are separate things instead of part of a whole.




Each cat has subcategories and those subcategories can have subcategories means