Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jean.luc
Starting Member
6 Posts |
 Posted - 2007-06-06 : 06:04:51
|
| I've found many ways, but i still don't know how to do it.I don't know how to write this SQL. Please help me.I think it's very hard to describe this question.in the table "nomenclatures" I need to get every record like this:1:i write SQL select compose,composant,qte_fabric,prix_achat from nomenclatures where compose='C17CCPN'; then i get two row:++++++++++++++++++++++++++++++++++++++++++++C17CCPN C17CC 1 0 C17CCPN RWSETPNCH 1 1.332 ++++++++++++++++++++++++++++++++++++++++++++2:i write SQL select compose,composant,qte_fabric,prix_achat from nomenclatures where compose='C17CC'; then i get rows:++++++++++++++++++++++++++++++++++++++++++++C17CC C17C 1 0 C17CC RCN 0.1 3.8 ++++++++++++++++++++++++++++++++++++++++++++3:i write SQL select compose,composant,qte_fabric,prix_achat from nomenclatures where compose='RWSETPNCH'; then i don't get rows:++++++++++++++++++++++++++++++++++++++++++++ no rows++++++++++++++++++++++++++++++++++++++++++++At this node, we stop to search this "RWSETPNCH" data node.4:i write SQL select compose,composant,qte_fabric,prix_achat from nomenclatures where compose='C17C'; ++++++++++++++++++++++++++++++++++++++++++++C17C C17 1 0 C17C RCN 0.1 3.8 ++++++++++++++++++++++++++++++++++++++++++++then, write SQL select compose,composant,qte_fabric,prix_achat from nomenclatures where compose='RCN'; continue to search ...and so on ......if the result have rows, we need to continue to search, unending search every node.if the result don't have rows, we could stop to search, stop to get rows.I need a result like this: union all of this records, then, write them into one temp table.How could i to do it? to write some functions? using one procedure to realign these data to one new temp table? or using one SQL sentence?My T-SQL knowledge is very pool, Please help meThank you very much! |
|
|
jean.luc
Starting Member
6 Posts |
Posted - 2007-06-06 : 21:03:15
|
| I'm waiting for your help.I need your helpThanks |
 |
|
|
jean.luc
Starting Member
6 Posts |
Posted - 2007-06-07 : 09:52:04
|
where is the expert?where is the people?Please help me. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
|
|
jean.luc
Starting Member
6 Posts |
Posted - 2007-06-08 : 09:24:43
|
| Sorry, i don't know why i open this "http://sqlblindman.googlepages.com/returningchildrecords" page, it's a blank page.why?sorryI need your helpthanksskype:washingtonhua |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-06-08 : 10:31:30
|
| It is not blank when I click on it. Perhaps there is a problem with your browser or your network security settings?Here are the contents:-------------------------------------------------Returning Child RecordsThe most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:CREATE TABLE [YourTable]([RecordID] [int] IDENTITY (1, 1) NOT NULL , [ParentID] [int] NULL)The challenge is to find a way to return all the child records and descendants for any given parent record.While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.A better algorithm is a method I call the "Accumulator Table".In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.Here is some skeleton code to show how it works:--This variable will hold the parent record ID who's children we want to find.declare @RecordID intset @RecordID = 13--This table will accumulate our output set.declare @RecordList table (RecordID int)--Seed the table with the @RecordID value, assuming it exists in the database.insert into @RecordList (RecordID)select RecordIDfrom YourTablewhere YourTable.RecordID = @RecordID--Add new child records until exhausted.while @@RowCount > 0insert into @RecordList (RecordID)select YourTable.RecordIDfrom YourTable inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordIDwhere not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)--Return the result setselect RecordIDfrom @RecordListThis method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5De4 d5 xd5 Nf6 |
|
|
|
jean.luc
Starting Member
6 Posts |
Posted - 2007-06-09 : 08:42:51
|
| Thanks "blindman", i'll try it again.But i still don't know how to do it.Could you give me a "stored procedure" or a "function" to resolve this question.The result could to be stored in the memory, a query, or a temp table.Thanks.skype:washingtonhua |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-06-11 : 10:28:27
|
| The stored procedure would implement the logic I gave you in the article. This forum is for advice, not free work. If you make an attempt at creating a stored procedure with the algorithm I outlined, you can post it and I or somebody else will help you debug it.e4 d5 xd5 Nf6 |
|
|
|
jean.luc
Starting Member
6 Posts |
Posted - 2007-06-14 : 10:51:11
|
| Ok, thanksI'll try to do it again. |
|
|
|
|
|
|
|
|
one deepness SQL search , i don't know how to do