CODEWITHSUNDEEP
sqlhierarchical-data
tempid
tempid

Reputation: 8198

SQL query to select parent and child names

I have a table with the following structure -

Category {Id, Name, ParentId}

I have values like this -

id  name               parentid
-------------------------------
1   Technology Focus   NULL
2   Tools              1
3   Database           1

How do I write a query that displays like this -

name (parent)      name (child)             
--------------------------------
Technology Focus   Tools
Technology Focus   Database
etc..

I believe I need to use the Group By clause but I'm not quite getting it.

Upvotes: 2

Views: 1466

Answers (3)

tereško
tereško

Reputation: 58444

If you are trying to implement at tree-like structure in SQL, then this, kinda, is the wrong way to do this.

You should use two tables to implement a tree:

CREATE TABLE Categories (
   category_id INT AUTO_INCREMENT,
   name VARCHAR(40),
   PRIMARY KEY (category_id)
);
CREATE TABLE Tree (
   ancestor INT NOT NULL,
   descendant INT NOT NULL,
   PRIMARY KEY(ancestor, descendant),
   FOREIGN KEY (ancestor) REFERENCES Categories(category_id),
   FOREIGN KEY (descendant) REFERENCES Categories(category_id)
);

This structure (know as Closure Table )is easier to maintain (preform updates, rearrange structure , etc. ).

Then you select the data like: 

SELECT 
   parent.name AS parent,
   item.name AS item
FROM Categories AS parent
LEFT JOIN Tree AS path ON parent.category_id = path.ancestor
LEFT JOIN Categories AS item ON item.category_id = path.descendant
WHERE parent.category_id = 1

Anyway , read about Closure Tables, you will understand why ..

Upvotes: 1

Brian Willis
Brian Willis

Reputation: 23854

You need to join the table on itself like this:

select Cat.Name, Par.Name
from category as cat
inner join category par on cat.id = par.id

Upvotes: 1

nathan gonzalez
nathan gonzalez

Reputation: 11987

if i'm looking at that correctly, i think you just need

select parent.name, child.name 
from category child
  inner join category parent
    on parent.id = child.parentid

Upvotes: 6

Related Questions