Selecting All Children From All Parents in mySQL

I have a simple table like this:

+------------+---------+-----------+--------------+
| comment_id | post_id | parent_id | comment_text |
+------------+---------+-----------+--------------+
|          1 |     200 |         0 |          a   |
|          2 |     200 |         0 |          b   |
|          3 |     200 |         1 |          c   |
|          4 |     200 |         1 |          d   |
|          5 |     200 |         0 |          e   |
|          6 |     200 |         2 |          f   |
|          7 |     200 |         2 |          g   |
|          8 |     200 |         0 |          h   |
|          9 |     200 |         0 |          i   |
|         10 |     200 |         1 |          k   |
+------------+---------+-----------+--------------+

• Column parent_id tells us that this comment is a reply to another comment with this id.

• Let's say there is only one level nesting of comments.

Now I need to ret开发者_C百科urn only first 5 parent comments and all of the children that belong to them.

I have a query for that but there is one problem.

    (
     SELECT c.* 
     FROM comments AS c 
     WHERE c.post_id = '200' AND parent_id='0'
     LIMIT 0,5
    )
    UNION 
    (
     SELECT c.*
     FROM comments AS c
     WHERE c.post_id = '200' AND c.parent_id IN 
     (
      SELECT c.comment_id
      FROM comments AS c 
      WHERE c.post_id= '200' AND parent_id='0'
      LIMIT 0,5
     )
    )

The problem is that my current mySQL version doesn't support LIMIT in sub-queries. Also it seems that I execute same SELECT query twice!

I'm sure there must be a way to do this more elegantly.

This is the most elegant way to do it: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/. The one issue you'll run into is that inserts will take some time as the other rows need to be updated; however, this can be avoided with some creativity. That article is a great start.

In the end I ended up using something like what Andomar suggested but without the subquery:

        (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        order by comment_id DESC limit 0, 4
        )
union
       (
        select  *
        from    comments c
        join    comments p
        on      c.parent_id = p.comment_id
        where   c.post_id = '200' 
                and c.parent_id != '0'
        )
        order by comment_id DESC

EDIT: As noted correctly by niaher this query returned a much larger result set than required. That makes this solution wrong.

Fortunately I have found another way to do it, much more elegant and cleaner.

Correct way to do it:

SELECT c.*
    FROM (
        SELECT comment_id
        FROM comments
        WHERE post_id = 200 AND parent_id = 0
        ORDER BY comment_id ASC
        LIMIT 0,5       
    ) AS q
JOIN comments AS c
    ON c.comment_id = q.comment_id OR c.parent_id = q.comment_id

I've tested this query, and it runs MUCH faster than the previous one.

Can you just do 2 selects?

Select * from comments where parentID = 0 limit 5

select * from comments where parentID IN (*list of the 5 parentIDs)

On my version of MySQL, you can use limit in subqueries:

select  *
from    (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        limit    0, 3
        ) s
union
select  c.*
from    (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        limit    0, 3
        ) p
join    comments c
on      c.parent_id = p.comment_id;

The duplicate select is hard to avoid without the with statement, which MySQL does not support. You could create a temporary table to store them:

drop table if exists top5comments;

create table top5comments
    select  *
    from    comments c
    where   c.post_id = '200' 
            and c.parent_id = 0
    limit    0, 3;

insert  top5comments
select  children.*
from    top5comments parents
join    comments children
on      children.parent_id = parents.comment_id;

select * from top5comments;

This would require modifications if the query can be run by multiple connections at the same time.

P.S. By the way, a limit statement is usually accompanied by an order by clause. Without the order by, you'll get arbitrary rows, and the results can differ from query to query.

Maybe you mean this:

SELECT
  *,
  (CASE WHEN parent_id = 0 THEN comment_id ELSE parent_id END) AS use_id
FROM
  comments
WHERE
  (CASE WHEN parent_id = 0 THEN comment_id ELSE parent_id END) IN (
    SELECT    comment_id
    FROM      comments
    WHERE     parent_id = 0
    ORDER BY  comment_id ASC
    LIMIT     3)
ORDER BY
  use_id ASC,
  parent_id;

What it does is take the three 'first' (ordered by comment_id ASC) parent comments and all their children and then sort the whole list by parent-/comment_id (parent first, children after).

The CASE WHEN THEN ELSE END is for SQLite (that's how I test such thingies).

-- edit

If these records exist (4 more than your 10):

comment_id  post_id  parent_id  comment
1           200      0          a
2           200      0  b
3           200      1  c
4           200      1  d
5           200      0  e
6           200      2  f
7           200      2  g
8           200      0  h
9           200      0  i
10          200      1  j
11          200      1  k
12          200      2  l
13          200      0  m
14          200      1  n

this would be the result:

comment_id  post_id parent_id   comment use_id
1        200    0   a   1
3        200    1   c   1
4        200    1   d   1
10       200    1   j   1
11       200    1   k   1
14       200    1   n   1
2        200    0   b   2
6        200    2   f   2
7        200    2   g   2
12       200    2   l   2
5        200    0   e   5