query to trace all parents

[PYCTAM]

Hi,

I have a table with filled out below data:

+------+-----+
|parent|child|
+------+-----+
|A |B |
|B |C |
|B |E |
|C |D |
|E |F |
|E |G |
+------+-----+

So I have to make a query which get all 'parent' values values for
given child value.

For example :
-----------------
If I have to get all parent values for 'D' child., query must get this
values : C, B, A.

If I have to get all parent values for 'F' child., query must get this
values : E, B, A.

If I have to get all parent values for 'C' child., query must get this
values : B, A.

If I have to get all parent values for 'B' child., query must get this
values : A only.
-----------------

Is it possible to create a query which will covers all above conditions
or not using only sql statement without UDF or stored procedures.

Any solutiuons?

Sincerely,
Rustam Bogubaev

[Erland Sommarskog]

PYCTAM (rb*******@bookinturkey.com) writes:

So I have to make a query which get all 'parent' values values for
given child value.

For example :
-----------------
If I have to get all parent values for 'D' child., query must get this
values : C, B, A.

If I have to get all parent values for 'F' child., query must get this
values : E, B, A.

If I have to get all parent values for 'C' child., query must get this
values : B, A.

If I have to get all parent values for 'B' child., query must get this
values : A only.
-----------------

Is it possible to create a query which will covers all above conditions
or not using only sql statement without UDF or stored procedures.

In SQL2000, no.

In SQL 2005, slated for release in November, there is support for
recursive queries.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PYCTAM]

in this hopeless situation problem is solved using UDF :

CREATE FUNCTION getAllParents(
@child NVARCHAR(1)
) RETURNS @PARENTS TABLE (
[parent] NVARCHAR(1)
) AS BEGIN
DECLARE @parent NVARCHAR(1)

SELECT @parent = parent FROM table WHERE child = @child

WHILE @@ROWCOUNT = 1 BEGIN
INSERT @PARENTS SELECT @parent

SELECT @child = @parent
SELECT @parent = parent FROM table WHERE child = @child
END

RETURN
END

[--CELKO--]

Again, get a copy of TREES & HIERARCHIES IN SQL and look up the Nested
Sets Model for trees.