Category and subcategory through a single query

Question

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.

this is the unlimited multilevel architecture, parent can be the id of the parent cat if it's root it will be 0 — khaled_webdev, May 21 '12 at 12:40
take a look on this [answer](http://stackoverflow.com/questions/4452472/category-hierarchy-php-mysql) // also see side related links — khaled_webdev, May 21 '12 at 12:43

k102 · Answer 1 · 2012-05-21T12:45:21.313

1

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

edited May 21 '12 at 12:45

answered May 21 '12 at 12:25

k102

Shouldn't you give `tp.name`, and `t.name` specific identifiers so you can return the result as an associative array? something like: `tp.name as 'parent_name'` and `t.name as 'child_name'` – Brook Julias May 21 '12 at 12:31
Yes `as` is used to name columns in a select statement. It is meant for instances such as this when you are returning two columns with the same name. When wouldn't be able to use the result as a associative array since 'name' will be set twice. – Brook Julias May 21 '12 at 12:42
@BrookJulias, sorry - i've deleted that comment as it seems i've read yours wrong. you are right, dunno why i said such a thing =) – k102 May 21 '12 at 12:44

score 0 · Answer 2 · edited Aug 17 '12 at 04:20

0

You can use following query:

SELECT c.name,pc.name FROM category c left join category pc on c.pid = pc.id;

edited Aug 17 '12 at 04:20

Himanshu

answered May 21 '12 at 12:32

Ganesh Bora

2 Answers2