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
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.