0

I'm writing a basic forum system for my site using MySQL. I need a way to track not only posts, but responses TO the posts. Since each post can only have ONE parent, but can ultimately have MULTIPLE children, I'm thinking I need two tables. The first table contains at a minimum the titles, the bodies and the parent of each post. The second table contains only a reference to PARENT posts and their corresponding CHILDREN, like so:

Posts Table

ID |  PARENT  |  TITLE  |  POST
1  |          |  Msg 1  |  Body 1
2  |          |  Msg 2  |  Body 2
3  |          |  Msg 3  |  Body 3
4  |  2       |  Msg 4  |  Body 4
5  |  1       |  Msg 5  |  Body 5
6  |          |  Msg 6  |  Body 6
7  |          |  Msg 7  |  Body 7
8  |  5       |  Msg 8  |  Body 8
9  |          |  Msg 9  |  Body 9
10 |  1       |  Msg 10 |  Body 10
11 |  1       |  Msg 11 |  Body 11
12 |  2       |  Msg 12 |  Body 12


Children Table

ID |  PARENT  |  CHILDREN   
1  |  2       |  4           
2  |  1       |  5 
3  |  5       |  8 
4  |  1       |  10 
5  |  1       |  11
6  |  2       |  12

In this example, Message 1 has 3 children: 5, 10 & 11. Message 2 has 2 children: 4 & 12. Etc.

Armed with this information, I'm trying to find the best way to determine the children of a subset of my Posts list (Say Messages 5 thru 10)? Would a JOIN of some sort allow a single query to get the parents and any corresponding children? Or am I better served doing separate queries on each parent message to determine its children?

I'm trying to get my head around JOIN and it's confusing as heck. Sorry if this is basic stuff, but thanks in advance.

ConleeC
  • 337
  • 6
  • 13
  • There's no need for both tables. The parent information in the first table is the same as in the second table. – Barmar Jun 27 '18 at 19:10
  • To get the parents *and* their children you need a `UNION`. One subquery just gets the parents, the other is a `JOIN` that gets their children. – Barmar Jun 27 '18 at 19:16
  • @Barmar I realize if I was querying the entire table, I'd be able to constitute all the dependencies. However, if you LIMIT your query, you can have parent posts that far precede the query range. And those parents, additionally, could have many other children, also preceding (or following) the query range. That is my problem. I'll read up on UNION. Also going to read up on SELF-JOIN. Does that have any application in this solution? Thanks in advance. – ConleeC Jun 27 '18 at 20:57
  • You would use a self-join if you just use the `Posts` table without the `Children` table. – Barmar Jun 27 '18 at 21:01
  • Thank you @Barmar. I'm focusing my study on `SELF-JOIN` right now, based on your guidance. But honestly it's hurting my brain. If you have any gentle guidance on how to structure such a query, given the example tables I've provided, I would be forever in your debt. If not, I'm certain I'll figure it out sometime in the next 6 months. :) – ConleeC Jun 27 '18 at 21:18
  • This is a faq. Please google clear statements of your question/problem/goal before considering posting. Google re sql recursive/heirarchical queries with & without 'mysql'. MySQL now has CTEs. – philipxy Jul 21 '18 at 21:06
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – philipxy Jul 21 '18 at 21:07

1 Answers1

1
Select parent, group_concat(title) from posts where 
parent!='' group by parent;

Start here and see what happens.

Bleach
  • 561
  • 4
  • 11
  • Thank you for your help. I'm still studying this, and will keep doing so, but an initial attempt has returned no result. – ConleeC Jun 28 '18 at 02:01
  • If your table and column names were correct, that query should return a result. – Bleach Jun 28 '18 at 02:15
  • You're absolutely correct. Not sure where my typo was before, but I definitely now get results. I have to dig into this and see how to limit my query to a subset of the list, and also return parents with no children. – ConleeC Jun 28 '18 at 05:45