Best Way For Join Parent ID

Question

I have a table named "categories" like this:

id              int(11)      NO  PRI  NULL  auto_increment
name            varchar(50)  NO       NULL   
seo_name        varchar(50)  NO  MUL  NULL   
parent_id       int(11)      NO       NULL   
total_projects  int(11)      NO       NULL   
order           int(11)      NO  MUL  NULL   

id and parent_id are columns related. parent_id refers rows relationships. For example I am querying like this

SELECT * FROM categories WHERE id = 99

But I want to get parent's category id (it is stored in parent_id) in same query. How can i do this? Thanks

Answer 1

SELECT * FROM Categories cat
INNER JOIN Categories parent
on cat.parent_id = parent.id

Is this what you mean?

Answer 2

Try this.

SELECT t1.* , t2.name as parent_category 
  from categories t1 
  INNER JOIN categories t2 on t1.id = t2.parent_id