query to trace all parents

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 BogubaevPYCTAM (rbogubaev@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||Again, get a copy of TREES & HIERARCHIES IN SQL and look up the Nested
Sets Model for trees.