Recursive query "SQL Server 2000"
-
I am working with "SQL Server 2000 SP3 2000.80.760.0". How to write recursive query for next example ? (I use .net c# to query database, and my knowledge of SQL is limited ...) All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then I execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
-
I am working with "SQL Server 2000 SP3 2000.80.760.0". How to write recursive query for next example ? (I use .net c# to query database, and my knowledge of SQL is limited ...) All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then I execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
Why you need to run query 200 Times ? There's way in SQL to achieve anything directly rather then looping it. it's not a good practice and having overhead to Query engine. Either way you can IN in SQL Like,
SELECT * from table where subpart_id IN (SELECT subpart_id FROM parts_table where part_id='1001')
Your question is not quite clear on what you want to exactly achieve.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Text -
I am working with "SQL Server 2000 SP3 2000.80.760.0". How to write recursive query for next example ? (I use .net c# to query database, and my knowledge of SQL is limited ...) All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then I execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
I still haven't finished thinking about this from when you asked it before in the C# forum. Did you try the TVPs? Oh, right 2000. :sigh:
-
Why you need to run query 200 Times ? There's way in SQL to achieve anything directly rather then looping it. it's not a good practice and having overhead to Query engine. Either way you can IN in SQL Like,
SELECT * from table where subpart_id IN (SELECT subpart_id FROM parts_table where part_id='1001')
Your question is not quite clear on what you want to exactly achieve.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Remove HTML Tag, get plain Text -
I am working with "SQL Server 2000 SP3 2000.80.760.0". How to write recursive query for next example ? (I use .net c# to query database, and my knowledge of SQL is limited ...) All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then I execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
I am rather certain that your given table structure requires looping. You can use either actual loops or procs (recursive) for this but the result is the same. Doing it in the database would probably be better than C# (per your other post.) If you can modify the table then there are probably other solutions. See the following link and drill down on the sublinks on it as there are other solutions. http://www.sqlteam.com/article/more-trees-hierarchies-in-sql[^] You might also try googling with the following: sql hierarchy query -cte