I have a self referential table:
ID|NAME|PARENT_ID
1 |P |NULL
2 |C1 | 1
3 |C2 | 1
4 |C3 | 2
5 |C4 | 4
I'm trying to write a query to get all the children of an ID (infinite levels) For example: When input is 1, i want all the rows which are descendants of 1 i.e
ID|NAME|PARENT_ID
1 |P |NULL
2 |C1 | 1
3 |C2 | 1
4 |C3 | 2
5 |C4 | 4
When input is 2:
ID|NAME|PARENT_ID
4 |C3 | 2
Please check this SQL Fiddle.
I have reached here so far:
select id as productid,name,@pv:=parent_id
from products
join (select @pv:=1)tmp
where parent_id=@pv
But it only gives me two level records, I need infinite levels of records.
Thank you, Sash
You can achieve this with the following query:
select id,
name,
parent_id
from (select * from products
order by parent_id, id) base,
(select @pv := '1') tmp
where find_in_set(parent_id, @pv) > 0
and @pv := concat(@pv, ',', id)
Here is a fiddle based on the one provided in the question.
The value specified in @pv := '1' should be set to the id of the parent you want to select all the descendants of.
This will work also if a parent has multiple children. However, it is required that for each record parent_id < id, otherwise the results will not be complete.
Also note that for very large data sets this solution might get slow, as the find_in_set operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.
NB: If you want to have the parent node itself also included in the result set, then prefix the following before the above SQL with the id value of interest in the where clause:
select id,
name,
parent_id
from products
where id = '1'
union
...
Alternative 1: CONNECT BY
Some other databases have a specific syntax for hierarchical look-ups, such as the CONNECT BY clause available on Oracle databases. MySql does not offer such a syntax.
Alternative 2: smarter identifiers
Things become a lot easier if you would assign id values that contain the hierarchical information. For example, in your case this could look like this:
ID | NAME
1 | P
1-1 | C1
1-2 | C2
1-1-1 | C3
1-1-1-1 | C4
Then your select would look like this:
select id,
name
from products
where id like '1-%'
Alternative 3: Repeated Self-joins
If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql like this:
select p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from products p1
left join products p2 on p2.id = p1.parent_id
left join products p3 on p3.id = p2.parent_id
left join products p4 on p4.id = p3.parent_id
left join products p5 on p5.id = p4.parent_id
left join products p6 on p6.id = p5.parent_id
where 1 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id)
order by 1, 2, 3, 4, 5, 6;
See this fiddle
The where condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.