Select item, and select name of parent

Question

Quickie:

Table structure:

id | name | parent_id

I want to run a query for ID's, but also return that item's parent_id and the name of that id as well.

To better explain

SELECT id, name FROM sections

...then for each ID, return the ID and name of its parent using the "parent_id" column.

I'm trying to get this into an array:

[0]
    [id]
    [name]
    [parent_id]
    [parent_name]

Answer 1

This should work:

SELECT s.id, s.name, s.parent_id, p.name as parent_name
FROM sections s LEFT JOIN sections p ON s.parent_id = p.id

Basically you just want to join the table against itself and bring in the name field as the parent name.

Answer 2

SELECT a.id, a.name, b.id as parent_id, b.name as parent_name
FROM sections a
INNER JOIN sections b on a.parent_id = b.id

(could also do an outer join if there are nodes without parents)

Answer 3

just join the table to itself

something like:

SELECT child.id, child.name, child.parent_id, parent.name AS parentname FROM tablename child LEFT JOIN tablename parent ON child.parent_id = parent.id

Answer 4

is doing a join more effective than simply doing subselects?

select id as childID , name as childName, parent_id as pid, (select id from sections where id = pid) as parentID, (select name from sections where id = pid) as parentName from sections;

the bigger issue is that this is non recursive... I'm curious how one would go about getting this to grow ad infinitum for relationships that are multi-generational.