CODEWITHSUNDEEP
mysqlsqlrecursive-query
Nicholas Mordecai
Nicholas Mordecai

Reputation: 879

SQL - Showing all parents from recursive relationship

I have a table for categories. This has a recursive relationship so that a category can become a subcategory of another category. The table looks like this:

id  name    short_desc  long_desc   tag_id  parent_id

I wrote simple to get sql to find all level 1 categories:

 SELECT * FROM category WHERE parent_id =0

Then I wrote a query to get all of the level 2 categories (where parent category doesn't have a parent)

SELECT * FROM category WHERE parent_id IN (SELECT id FROM category WHERE parent_id =0)

What I would like to do, is produce a column where is shows all category data and any relevant parent category.

Logically like this:

  1. select all from category
  2. if parent_id != 0, add the parent as a new row
  3. repeat 2 until all parents have been accounted for.

The result should look something like this:

id  name    short_desc  long_desc   tag_id   parent_name  parent_name_2

if the parent_name is null / empty, then parent_name should remain empty. if there is a parent_name id in the field, then check to see if there is a parent_name_2 and if so, populate both columns, if not then only populate parent_name.

I do have the option of coding this in jquery or php which I have a good idea how to do. However, I am sure that I can get the data I need from a good SQL query.

Any help would be greatly appreciated.

Kind Regards

Nick

Upvotes: 1

Views: 493

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Here's one option using multiple outer joins:

select c.*, 
  case when c2.id is not null then c2.name end parent_name,
  case when c3.id is not null then c3.name end parent_name_2
from category c
  left join category c2 on c.parent_id = c2.id
  left join category c3 on c2.parent_id = c3.id

Upvotes: 2

Related Questions