CODEWITHSUNDEEP
mysql
c.k
c.k

Reputation: 1105

Show the parent post through child post content

I would like to show the parent post through its children post by filtering the content of the child post/parent post. I have a table message. And this is how it looks like:

m_id   m_parent_post_id   m_user_id  m_topic  m_message  m_status  m_by_admin

1           0               1234      t1       first..    replied      0
2           1               1234      null     second..   na           0
3           1               1234      null     third..    na           1
4           1               1234      null     fourth..   na           0
5           0               2222      t2       first..    replied      2
6           5               2222      null     second     na           0
7           0               1111      t3       second     new          0

Table explanation:

So in here, I am trying to select all the message which m_message contains second string or words and it (the rows not the parent id) should be only created by the user. After selecting it, it should show only the parent post of it wherein the m_status is replied.

SELECT * FROM message where m_message LIKE '%second%' WHERE m_by_admin = 0 AND

.... Did not know what's next.

This will only show the child post, not the parent. Should show the m_id 1 and 5 as expected output.

 1           0               1234      t1       first..    replied      0
 5           0               2222      t2       first..    replied      2

 7           0               1111      t3       second     new          0

Need help with this. Any faster query if possible, I have hundreds of rows of parent and child post.

Upvotes: 0

Views: 54

Answers (1)

Sinto
Sinto

Reputation: 3997

SELECT `P`.* FROM message P WHERE 
`P`.`m_id` IN (SELECT `C`.`m_parent_post_id` FROM message C 
WHERE `C`.`m_message` LIKE '%second%' AND `C`.`m_by_admin` = 0)
OR (`P`.`m_message` LIKE '%second%' AND `P`.`m_by_admin` = 0 AND `P`.`m_parent_post_id` = 0);

JOIN user table as:

SELECT `P`.*, `U`.`user_id` FROM message P 
JOIN user U ON (`U`.`user_id` = `P`.`m_user_id`) 
WHERE `P`.`m_id` IN 
    (SELECT `C`.`m_parent_post_id` FROM message C 
    WHERE `C`.`m_message` LIKE '%second%' AND `C`.`m_by_admin` = 0) 
    OR (`P`.`m_message` LIKE '%second%' AND `P`.`m_by_admin` = 0 AND `P`.`m_parent_post_id` = 0);

Upvotes: 1

Related Questions