Select Parent and Children With MySQL

Question

I know this question comes up often, but today I can't find the answer I'm looking for. I have a table with this schema.

CREATE TABLE `comments` (
    `id` bigint(10) unsigned not null auto_increment,
    `parent_id` bigint(10) unsigned default 0,
    `date_sent` datetime not null,
    `content` text not null,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

I'd like to select parent rows, and the children of those rows. I don't allow children to have children, so it's just one parent, with any number of children.

I think I've seen this done with unions before, or inner joins.

Answer 1

Are you looking for

SELECT p.id, child.*
FROM comments p
INNER JOIN comments child ON (child.parent_id = p.id)
WHERE ....

UPDATE
Or LEFT JOIN if you want to see rows with no parents

Answer 2

Parents are records with no parent_id.
Children have parent_id equal to the parent comment's id.

  SELECT ...
    FROM comments AS parent
         LEFT JOIN comments AS child 
         ON child.parent_id = parent.id
   WHERE parent.parent_id IS NULL
ORDER BY parent.id, child.id;

Note that the self-join should be an outer join so that you don't miss parent comments with no children.