How to left join or inner join a table itself

Question

I have this data in a table, for instance,

id      name        parent      parent_id
1       add         self        100
2       manage      null        100
3       add         10          200
4       manage      null        200
5       add         20          300
6       manage      null        300

How can I left join or inner join this table itself so I get this result below?

id      name        parent
2       manage      self
4       manage      10
6       manage      20

As you can I that I just want to query the row with the keyword of 'manage' but I want the column parent's data in add's row as the as in manage's row in the result.

Is it possible?

EDIT:

the simplified version of my actual table - system,

system_id   parent_id   type    function_name       name        main_parent         make_accessible     sort
31          30          left    main                Main        NULL                0                   1
32          31          left    page_main_add       Add         self                0                   1
33          31          left    page_main_manage    Manage      NULL                0                   2

my actual query and it is quite messy already...

SELECT 
    a.system_id,
    a.main_parent,
    b.name, 
    b.make_accessible, 
    b.sort

FROM system AS a

INNER JOIN -- self --
(
    SELECT system_id, name, make_accessible, sort
    FROM system AS s2

    LEFT JOIN -- search --
    (
    SELECT system_id AS parent_id
    FROM system AS s1
    WHERE s1.function_name = 'page'
    ) AS s1

    ON s1.parent_id = s2.parent_id

    WHERE s2.parent_id = s1.parent_id
    AND s2.system_id != s1.parent_id
    ORDER BY s2.sort ASC
) b
ON b.system_id = a.parent_id


WHERE a.function_name LIKE '%manage%'
ORDER BY b.sort ASC

result I get currently,

system_id   main_parent     name    make_accessible sort
33          NULL            Main    0                1

but I am after this,

system_id   main_parent     name    make_accessible sort
33          self            Main    0                1

Solution 1

You just need to reference the table twice:

select t1.id, t1.name, t2.id, t2.name
from TableA t1 
  inner join TableA t2
    on t1.parent_id = t2.Id

Replace inner with left join if you want to see roots in the list.

UPDATE:

I misread your question. It seems to me that you always have two rows, manage one and add one. To get to "Add" from manage:

select system.*, (select parent 
                    from system s2 
                   where s2.parent_id = system.parent_id 
                     and s2.name = 'add') 
                 AS parent
from system
where name = 'manage'

Or, you might split the table into two derived tables and join them by parent_id:

select *
  from system
  inner join
  (
     select * from system where name = 'add'
  ) s2
    on system.parent_id = s2.parent_id
  where system.name = 'manage'

This will allow you to use all the columns from s2.

Solution 2

Your data does not abide to a child-parent hierarchical structure. For example, your column parent holds the value 10, which is not the value of any id, so a child-parent association is not possible.

In other words, there's nothing that relates the record 2,manage,null to the record 1,add,self, or the record 4,manage,null to 3,add,10, as you intend to do in your query.

To represent hierarchical data, you usually need a table that has a foreign key referencing it's own primary key. So your column parent must reference the column id, then you can express a child-parent relationship between manage and add. Currently, that's not possible.

Comments

[Run]

sorry, maybe I should have provided more details and the actual table I am working on. please have a look on my edit above. thanks.

[Run]

thanks for the edit and the answer. tested it and got it right. thanks!