Get all children by parent id and where clause in mysql

Question

I have a table that stores id and parent_id in same table. I want a recursive query that accepts parent_id as an argument and returns all child nodes with nth level. For this I am using this code and working properly for me.

select  id,
        name,
        parent
from    (select * from tablename
         order by parent, id) tablename,
        (select @pv := '1') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

My problem start from here: I want to add WHERE clause with result set but unable to do this. In result set I am getting user type like 'admin', 'editor'.

I want to remove 'editor' user type from result set. Let me know if possible to how to get this?

Could you add sample data for the table and the corresponding output you expect for it? — trincot, Dec 20 '18 at 12:32
Could you also specify what should happen if the condition is false for a certain record, but is true for the child records that have it as parent? Should the children be included or excluded? — trincot, Dec 20 '18 at 12:33
@trincot result set returns all child excluding parent and i want that where clause work with child data. Ex. WHERE user_type <> 'Editor' should return all child without editor user type. — Katty, Dec 20 '18 at 12:41
Yes, but there can be grandchildren also in the result set. Should grandchild be included if the intermediate child is excluded? — trincot, Dec 20 '18 at 12:42
children or grandchildren should be removed if user type not matched — Katty, Dec 20 '18 at 12:45
Sorry, but my question is not that simple as that. Imagine a child that is editor (should be excluded -- no problem, I understand). But that child has children also, which are *not* editors. Should those be included? — trincot, Dec 20 '18 at 12:49
OK, in that case I had to update my answer: that is done now. — trincot, Dec 20 '18 at 13:02
I recommend you to try ClosureTable structure for easily querying on hierarchical data — Praveen E, Dec 20 '18 at 13:27
@trincot I am unable to understand your query (how the syntax worked), can you please provide me reference URL for further study? or more explanation? — jagad89, Jan 23 '20 at 11:34
@jagad89, if your comment is about my answer then it would have been better placed there. You can find more info about hierarchical queries in MySql in [this Q&A](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query). — trincot, Jan 23 '20 at 11:59

trincot · Accepted Answer · 2018-12-21T09:05:01.937

There are two interpretations possible. From a recent comment I understand you need the first one:

Exclude children of excluded parents

So even if children are not editors, if one of their ancestors is an editor they should be excluded. That means you should exclude records in the inner most query: add the where there:

select  id,
        name,
        parent_id,
        user_type
from    (select * from p
         where user_type <> 'editor'
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Include children of excluded parents

In this interpretation you want editor children to be included irrespective of whether any of their ancestors are to be excluded.

Add the user_type field in the select list and then wrap that query that performs the filter, like this:

select  *
from    (
        select  id,
                name,
                parent_id,
                user_type
        from    (select * from p
                 order by parent_id, id) products_sorted,
                (select @pv := '19') initialisation
        where   find_in_set(parent_id, @pv)
        and     length(@pv := concat(@pv, ',', id))
) as sub
where user_type <> 'editor'

So again, here the result will include also records of which the parent-hierarchy (parent, grandparent, grand-grandparent, ...) might not be completely included (because some of those could be editor).

Sorry for delay. May be I was unable to understand your comment queries properly but you catch my requirement perfectly. your second answer is what I want so thank you. — Katty, Dec 24 '18 at 07:28

score 0 · Answer 2 · answered Dec 20 '18 at 12:44

0

I think it'll be easier to create a joint rather then using sub-queries, but without seeing the design of tables you are using I'm afraid I can't really give you good example.

answered Dec 20 '18 at 12:44

Yuriy Semenikhin

90
5

Get all children by parent id and where clause in mysql

2 Answers2

Exclude children of excluded parents

Include children of excluded parents

Linked