Category and sub-category by a single query

advertisements

I have a table with fields id,pid,name.I want to fetch the data in the following format through a single query.

id- parent category name- name

Name would be either category or subcategory and parent category name would be none if its parent else will be parent category name..

Its urgent and important for me.please help me..i will appreciate it.thanks


try this:

select t.id,
        tp.name as parent_category,
        t.name as category
from table t
full join table tp on tp.id = t.pid