CODEWITHSUNDEEP
phpmysql
Anish Joseph
Anish Joseph

Reputation: 1026

Mysql query to get parent details in a single query

I have a table where i store categories with its parent's id. Following is the table structure What i need is to get the parent and details of id 4 in a single query without php recursion

id | CategoryName | parentid
1      Web            0
2      Software       0
3      PHP            1
4      Arrays         3

Upvotes: 0

Views: 1840

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270765

A self join should be sufficient here. Your goal is to join the table against itself, while relating the parentid of the main row to the id of its parent's row.

SELECT 
  me.id AS me_id,
  me.CategoryName AS me_category,
  parent.id AS parent_id,
  parent.CategoryName AS parent_category
FROM
  tablename me JOIN tablename parent ON me.parentid = parent.id
WHERE me.id = 4

Upvotes: 3

Jaydee
Jaydee

Reputation: 4158

Would a variation of 

select * from categories t1 join categories t2 where t1.parentid=t2.id

work for you

Upvotes: 0

Related Questions