SQL : Get child of of an element

Question

I have a MySQL table of following structure.

**Table elements :**

element_id  element_name    parent_id
1           UIG             0
2           CAM             1
3           IHG             1
4           USR             1
5           DBL             1
6           APD             1
7           RTM             1
8           OCR             2
9           IRT             3
10          ICR             3
11          OCR             2
12          USH             1
13          AML             1

I need to find child elements of a given element.

I made the following query :

SELECT parent_id,GROUP_CONCAT(element_id)
FROM elements
WHERE parent_id='1'
GROUP BY parent_id

which returns,

+-----------+--------------------------+
| parent_id | GROUP_CONCAT(element_id) |
+-----------+--------------------------+
|         1 | 2,3,4,5,6,7,12,13        | 
+-----------+--------------------------+
1 row in set (0.00 sec)

While I need also need the childs of element 2 and 3, which should result into

+-----------+------------------------------------+
| parent_id | GROUP_CONCAT(element_id)           |
+-----------+------------------------------------+
|         1 | 2,3,4,5,6,7,8,9,10,11,12,13        | 
+-----------+------------------------------------+
1 row in set (0.00 sec)

How do I achieve this without procedures and just a query?

Ciarán · Answer 1 · 2013-01-02T10:05:20.093

1

Use an IN () predicate...

SELECT '1' As Parent_Id,GROUP_CONCAT(element_id)
  FROM elements 
 WHERE Parent_Id IN ('1','2','3')
 GROUP BY '1';

Some database require the GROUP BY in this scenario others don't but it's fairly good practice to include it even if it is not needed.

Incidentally, I suspect Parent_Id is a numeric of some sort in which case it should really read...

SELECT 1 As Parent_Id,GROUP_CONCAT(element_id)
  FROM elements 
 WHERE Parent_Id IN (1,2,3)
 GROUP BY 1;

You could actually exclude the 1 As Parent_Id and GROUP BY 1 completely.

If you can only specify one value use a sub-query...

SELECT GROUP_CONCAT(element_id)
  FROM Elements 
 WHERE Parent_Id IN (SELECT Element_Id
                        FROM Elements
                       WHERE Parent_Id = 1)

edited Jan 02 '13 at 10:05

answered Jan 02 '13 at 09:41

Ciarán

3,017
1
16
20

Ah. It might have been useful to include that fact in your question. In which case you will need a sub-query. I have amended my answer to reflect this. – Ciarán Jan 02 '13 at 09:50
Yeah well, I guess that was obvious. Sorry for that... :) – user1263746 Jan 02 '13 at 09:53
I'm afraid it wasn't obvious at all, but never mind these things happen. :-) – Ciarán Jan 02 '13 at 09:55
So what's the possible solution? I am sorry for the goof up. – user1263746 Jan 02 '13 at 09:59

bonCodigo · Accepted Answer · 2013-01-02T11:54:03.067

1

You just need to use the in operator on a subquery.

select group_concat(element_id)
from chpr
where parent_id in (
  select group_concat(element_id)
from chpr
where parent_id = 1
group by parent_id)
;

** AS per OP's comment the levels are definitely a concern**

However based on the initial sample data and request, here is the SQLFIDDLE DEMO that provides the results as per the question's expected output.

The only change is that one needs to group by both element_id and parent_id the innner most first subquery.

Not a very elegant query at all:

select 1 as parent_id, group_concat(x.element_id)
from (
(select element_id
from chpr
where parent_id in
(select element_id
from chpr
where parent_id = 1
group by element_id, parent_id
))
union all
(select element_id
from chpr
where parent_id = 1
group by element_id, parent_id
)) x
;

Results:

PARENT_ID   GROUP_CONCAT(X.ELEMENT_ID)
1           8,9,10,11,2,3,4,5,6,7,12,13

edited Jan 02 '13 at 11:54

answered Jan 02 '13 at 09:46

bonCodigo

14,268
1
48
91

I guess it will not work where the level of child is more then two. – user1263746 Jan 02 '13 at 09:48
I think you need to expand your question to include all the not obvious bits and the assumptions you've made about our understanding. – Ciarán Jan 02 '13 at 09:57
Sorry I was away. Check out this extremely ugly code in [SQLFIDDLE DEMO](http://sqlfiddle.com/#!2/11f22/1) I have a very slow connection at the moment so unable to update the answer somehow. @Ciarán I too got the initial impression that OP just needs the extra parent_ids. – bonCodigo Jan 02 '13 at 10:16
@user1263746 can you take a look at this demo and comment please? – bonCodigo Jan 02 '13 at 10:24
Sorry, it fails after second level. Thanks for your efforts. – user1263746 Jan 02 '13 at 10:53
What is your second level? Are you planning to search *zig zag* till when? ;) In the question at initial level didn't see you mentionining multiple levels. You are better off witha procedure of a function in this case. – bonCodigo Jan 02 '13 at 11:08
Stored procedure it is then... Thanks! – user1263746 Jan 02 '13 at 11:30

score 0 · Answer 3 · edited May 23 '17 at 12:18

0

Take a look at my this question

Finding all parents in mysql table with single query (Recursive Query)

And here is the source for this kind of work.

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

edited May 23 '17 at 12:18

Community

1
1

answered Jan 02 '13 at 09:39

Muhammad Raheel

19,823
7
67
103

I don't think the OP is trying to read the parents. It's the childs of a specific set of parents that's the required result set. – Ciarán Jan 02 '13 at 09:47
well reversing the process in explain extended link can do anything thats why i posted the link of explainextended – Muhammad Raheel Jan 02 '13 at 09:52

SQL : Get child of of an element

3 Answers3