0

My schema:

id:int | post_hash:str | post_body: str | is_op: tinyint(1) | parent_id:int

1 | 'dddba11f43d90117b01' | 'test post' | 1 | Null 

2 | Null                  | 'test reply'| 0 | 1

This is to store forum posts and replies. How can I select post_hash and all its child posts specified by parent_id? To clarify, the idea is something like:

SELECT p.id FROM posts p WHERE post_hash = ? or parent_id = p.id

where p.id is the result of the select. It's self-referential so a subquery or join will be needed but I'm not sure how to go about this one.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
xendi
  • 2,332
  • 5
  • 40
  • 64
  • Please [edit] your question to include a bigger example of what you want to read from the table. Add several example rows to your table and write the result set you want from that table. Also please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query for SQL related questions. – Progman Jan 01 '20 at 18:12

2 Answers2

1

I have a doubt that using only UNION as suggested by Slaasko, is generic enough solution for this problem. So if you are using MySQL version 8.0 or higher, You may try using a recursive query approach, which will work even if you have multiple child -

WITH RECURSIVE post_det(id, is_op, parent_id) AS(
SELECT id, is_op, parent_id
FROM posts
WHERE post_hash = 'dddba11f43d90117b01'
UNION ALL
SELECT PD.id, PD.is_op, PD.parent_id
FROM posts P
INNER JOIN post_det PD ON P.parent_id = PD.is_op)
SELECT * FROM post_det;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
0

Use UNION to combine result sets from the parent and child queries:

SELECT id 
FROM posts
WHERE post_hash = ? 
UNION
SELECT p.id 
FROM posts p 
  JOIN posts pp on pp.id=p.parent_id
WHERE pp.post_hash = ?
slaakso
  • 8,331
  • 2
  • 16
  • 27