MySQL- Fetching multiple records from same table

Question

First of all, sorry for the bad title. I am in little hurry and couldn't think of a better one.

Now I have a table like this -

category_id    name        parent_category_id
______________________________________
1              country     0
2              state       1
3              city        2
4              block       3

And I want results as -

Name        ParentCategory
___________________________
country     NULL
state       country
city        state
block       city

Now the logic is ParentCategory is category name whose category_id is equal to parent_category_id. (Not sure if I explained it properly) I tried a lot writing a query for this, but don't know how can I do it.

Any help would be most appreciated.

LSerni · Accepted Answer · 2012-08-30 13:11:31Z

3

You want a JOIN of the table with itself:

SELECT child.name, parent.name FROM tbl AS child
    LEFT JOIN tbl AS parent
    ON (child.parent_category__id = parent.category_id);

answered Aug 30, 2012 at 13:11

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2012-08-30 13:11:34Z

3

SELECT 
  child.name AS Name,
  parent.name AS ParentCategory
FROM
  yourTableName AS child
  LEFT JOIN yourTableName AS parent ON parent.category_id=child.parent_category_id

answered Aug 30, 2012 at 13:11

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Comments

Himanshu · Accepted Answer · 2012-08-30 13:15:16Z

1

SELECT   c.name  Name,
         p.name  ParentCategory
FROM  t  c
LEFT JOIN t  p 
ON p.category_id=c.parent_category_id;

See this SQLFiddle

answered Aug 30, 2012 at 13:15

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Comments

lvil · Accepted Answer · 2012-08-30 13:11:44Z

-1

SELECT t1.name, t2.name FROM table as t1 LEFT JOIN table as t2 ON t1.parent_category_id=t2.category_id

answered Aug 30, 2012 at 13:11

lvil

4,3169 gold badges52 silver badges79 bronze badges

1 Comment

lvil Over a year ago

and this one (left join instead inner)?

Collectives™ on Stack Overflow

MySQL- Fetching multiple records from same table

4 Answers 4

Comments

Comments

See this SQLFiddle

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related