CODEWITHSUNDEEP
mysqlsql
mellowsoon
mellowsoon

Reputation: 23251

Select Parent and Children With MySQL

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.

Upvotes: 11

Views: 27167

Answers (3)

jmail
jmail

Reputation: 6134

I've not saying exactly, i think your getting this kind of problem as following as:

Ordered_Item

ID | Item_Name
1  | Pizza
2  | Stromboli

Ordered_Options

Ordered_Item_ID | Option_Number | Value
        1               43         Pepperoni
        1               44         Extra Cheese
        2               44         Extra Cheese

Output

ID | Item_Name | Option_1 | Option_2
1    Pizza       Pepperoni  Extra Cheese
2    Stromboli     NULL     Extra Cheese

And the my suggestions to use this method resolve this problem as there following as:

  1. The easiest way would be to make use of the GROUP_CONCAT group function here..
select 
        ordered_item.id as `Id`, 
        ordered_item.Item_Name as `ItemName`,

  GROUP_CONCAT(Ordered_Options.Value) as `Options`

  from ordered_item, ordered_options

  where ordered_item.id=ordered_options.ordered_item_id

  group by ordered_item.id

Which would output:

Id              ItemName       Options
1               Pizza          Pepperoni,Extra Cheese
2               Stromboli      Extra Cheese

That way you can have as many options as you want without having to modify your query.

Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:

SET SESSION group_concat_max_len = 8192;

Upvotes: 2

a1ex07
a1ex07

Reputation: 37364

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

Upvotes: 4

mechanical_meat
mechanical_meat

Reputation: 169304

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.

Upvotes: 25

Related Questions