Getting rows from a database then modify them with additional rows from database

Question

Here's the table structure

id | name | parent_id
1    fruit      0
2    apple      1
3    banana     1
4    orange     1
5    vegetable  0
6    carrot     5
7    tomato     5

I need get every row where a parent_id is 0, but I also need to to set an array on each row which is equal to 0 with the name of all it's children.

So I would have something like:

id = 1, name = fruit, children = [apple, banana, orange]

I'm aware there are better table structures but I must use the table structure stated.

I've tried getting all rows from the db then looping through them, if parent_id = 0 then push that to an array, otherwise it's a child so find parent in array and add it to that.

But there must be a better way?

tungbk29 · Accepted Answer · 2014-06-17 09:07:56Z

1

I thinks you should use:

SELECT pr.id, pr.name, ( SELECT GROUP_CONCAT(name) FROM test as ch WHERE ch.parent_id = pr.id GROUP BY ch.parent_id ) AS children FROM test as pr WHERE parent_id = 0

answered Jun 17, 2014 at 9:07

tungbk29

861 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

panthro Over a year ago

Very good. Thanks. Is there a way to include a string inside the children field? eg. we have apple,banana,pear etc. I woudl like to put in name:apple, name: banana

tungbk29 Over a year ago

You use: GROUP_CONCAT('name:', name)

MAQU · Accepted Answer · 2014-06-17 09:43:22Z

1

Without thinking about performance or beauty:

SELECT t.id, name, (
  SELECT GROUP_CONCAT(name) FROM table WHERE parent_id = t.id GROUP BY parent_id
) AS children 
FROM table t
WHERE parent_id = 0

edited Jun 17, 2014 at 9:43

MAQU

5622 silver badges12 bronze badges

answered Jun 17, 2014 at 8:52

colburton

4,7252 gold badges28 silver badges41 bronze badges

2 Comments

MAQU Over a year ago

small mistake in the inner select. parent_id shouldn't be 0, it should be equal to id from outer select.

MAQU Over a year ago

give the outer table an alias and use its alias.id for the inner select. edited his answer

Kickstart · Accepted Answer · 2014-06-17 11:58:36Z

1

Avoiding a sub query, just using a self join the following should do it:-

SELECT t1.id, t1.name, GROUP_CONCAT(CONCAT('name:', t2.name)) 
FROM test t1
LEFT OUTER JOIN test t2
ON t1.id = t2.parent_id
WHERE t1.parent_id = 0
GROUP BY t1.id, t1.name

SQL fiddle for it:-

http://www.sqlfiddle.com/#!2/d4479a/1

edited Jun 17, 2014 at 11:58

answered Jun 17, 2014 at 9:47

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

1 Comment

MAQU Over a year ago

this is even a better solution

user3747704 · Accepted Answer · 2014-06-17 08:56:56Z

0

I had come across a similar issue, where I had to fetch all subordinates of a user in a recursive manner. We wrote a function where we loop through each of the child records recursively until we reach the final subordinate. We keep on concatenating the subbordinates we find in each loop.

Hope through this you can think of a solution and resolve your problem.

answered Jun 17, 2014 at 8:56

user3747704

12 bronze badges

Collectives™ on Stack Overflow

Getting rows from a database then modify them with additional rows from database

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related