Microsoft SQL Server forum - query to trace all parents

Author

query to trace all parents

PYCTAM

2005-06-23, 1:23 pm

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

2005-06-23, 8:23 pm

PYCTAM (rbogubaev@bookintur
key.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

PYCTAM

2005-06-24, 11:24 am

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--

2005-06-24, 8:23 pm

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

Sponsored Links