CODEWITHSUNDEEP
mysqlsqljoinselectrecursive-query
Jc John
Jc John

Reputation: 1859

Getting the details of parent id from child id in mysql

I am creating a messaging application. I have a table with a parent id to identify that it is a child of the primary key message. Now I want to get the details of the parent and I have an available id of the child. Something like selecting the value of parent = 0 with condition id = 28.

Here is the structure and sample data of my table.

                     message_tbl
 id          message          date               parent_msg
 27          hello     2020-05-24 15:03:40           0
 28          world     2020-05-24 15:04:17           27

I want a result of like this:

27          hello     2020-05-24 15:03:40           0

Here is sqlfiddle for the structure: http://sqlfiddle.com/#!9/2b18d2

Upvotes: 0

Views: 512

Answers (1)

GMB
GMB

Reputation: 222432

For a one-level relationship, a self join will do:

select mp.*
from message_tbl mp
inner join message_tbl mc on mc.parent_msg = mp.id
where mc.id = 28

If you have a variable number of levels, then you can use a recursive query (available in MyQSL 8.0 only):

with recursive cte as (
    select * from message_tbl where id = 28
    union all
    select m.*
    from message_tbl m
    inner join cte c on c.parent_msg = m.id
)
select * from cte where parent_msg = 0

Upvotes: 1

Related Questions