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.

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>