how can we write mysql query where parent id have a child id and next time child id is a parent id how can i do?

how can i built the query where parent id have a five child id and after that child id is a parent id and have a five child id.how can i call in our query.table just only one.kindly help me about this confusion.thanks Regards imadbaloch开发者_如何学JAVA

I don't know if I got you right, but I think so: You'd like to have some recursive query to ask for an entity with some child entities, which on their part have child entities as well (grandchild entities).

Unfortunately, MySQL does not support recursion within the queries.

You have to do it manually using multiple queries to ask for the child entities' child entities (grandchild entities) and their child entities (grandgrandchild entities) and so on.

Update: If the depth is fixed, say, you are interested in the direct child entities and the grandchild entities (and no more), then you could perform two joins, e.g.:

SELECT e.id, e.name, ce.id, ce.name, gce.id, gce.name
FROM <table> AS e
LEFT JOIN <table> AS ce ON ce.parent_id = e.id
LEFT JOIN <table> AS gce ON gce.parent_id = ce.id

You could do a self join to get the child of a child. Assuming a table like:

Id    ChildId    ....
----- ------- -----
....

Something like:

SELECT a.Id as EntryId, a.ChildId as ChildId, b.ChildId as GrandchildId
FROM <tablename> a INNER JOIN <tablename> b ON a.ChildId = b.Id

The downside is that for each level of hierarchy you want to include you need another join.

UPDATE

Responding to comments.

This is not one grandchild per parent. If the parent has 5 children, and each of those has 4 children, then you'll get a total of 20 rows returned.

To get full detail of the grandchildren you'll need to self-join again, like this:

SELECT a.Id as EntryId, a.ChildId as ChildId, b.ChildId as GrandchildId, c.*
FROM (<tablename> a INNER JOIN <tablename> b ON a.ChildId = b.Id)
    INNER JOIN <tablename> c ON b.ChildId = c.Id

Try Below query :

DECLARE @id INT
SET @id = 5 

CREATE TABLE #temp (id INT , ParentId INT)

INSERT INTO #temp VALUES(1,0);

INSERT INTO #temp VALUES(2,1);

INSERT INTO #temp VALUES(3,2);

INSERT INTO #temp VALUES(4, 3);

INSERT INTO #temp VALUES(5,4);

WITH parent AS
(
   SELECT id, parentId  from #temp WHERE id = @id
   UNION ALL 
   SELECT t.id, t.parentId FROM parent
   INNER JOIN #temp t ON t.id =  parent.parentid
)

SELECT id,ParentId FROM parent
WHERE id <> @id;    

Drop TABLE #temp;