471,073 Members | 1,156 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

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 Bogubaev

Jul 23 '05 #1
3 3443
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
Jul 23 '05 #2
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

Jul 23 '05 #3
Again, get a copy of TREES & HIERARCHIES IN SQL and look up the Nested
Sets Model for trees.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by dr zoidberg | last post: by
reply views Thread by John Macon | last post: by
4 posts views Thread by srussell705 | last post: by
2 posts views Thread by Lumpierbritches | last post: by
1 post views Thread by Lumpierbritches | last post: by
3 posts views Thread by wjreichard | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.