Retrieve records based on dependency

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Retrieve records based on dependency

Author

Topic

Newkid
Starting Member

7 Posts

Posted - 2006-09-11 : 10:46:39

Hi there!

Any guidlines would be great help. Please consider this senerio

stage_number | stage_id | stage_dependency
1 200
2 201 1
3 202 2
4 203 1
5 204 4

I want to know which stages are dependent on which which stage_id not stage_number. For instance in the above case, Stage 201,203 is dependent on stage 200,Stage 204 is dependent on stage 2003 etc

In nutshell, I want to retreive stage_id in stage dependency column.

Hope am making sense.

With Thanks!
Newkid

With Thanks !
Newkid

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 10:50:23

see if this helps

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Chirag

Newkid
Starting Member

7 Posts

Posted - 2006-09-11 : 11:16:25

Thanks for the URL.

I don't think that would sort out my issue. It is too complex I guess and mine one is simple problem..

With Thanks!
Newkid

With Thanks !
Newkid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-11 : 11:22:23

is this what you want ?


select d.stage_id
from   table s inner join table d
       on s.stage_number = d.stage_dependency
where  s.stage_id = 200

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 11:34:48

I suspect Newkid is looking for something like this. Basically, the same as KH's without the where clause.


set nocount on
declare @tb table
	(stage_number int
	,stage_id varchar(10)
	,stage_dependency int)

insert @tb (stage_number, stage_id, stage_dependency)
select 1, '200', null union all
select 2, '201', 1 union all
select 3, '202', 2 union all
select 4, '203', 1 union all
select 5, '204', 4


select	a.stage_id, b.stage_id dependent_stage_id
from	@tb a
join	@tb b on b.stage_dependency = a.stage_number


output:
stage_id   dependent_stage_id 
---------- ------------------ 
200        201
200        203
201        202
203        204

Be One with the Optimizer
TG

Newkid
Starting Member

7 Posts

Posted - 2006-09-12 : 01:27:58

Thanks TG and Khatan for the input.

My delimma is all the records is in the same table. I have tried all the options to retreive the information as suggested by you with SELF JOIN but it fails.

Apart from the above the data types varies among themselves.

RT_DEPENDENCY = NVARCHAR(5) { In case of multiple dependency it stores information like 2,3,4 etc and RT_RDS_ID is int and RT_STAGE_ORDER is also int

Please guide me in case of multiple dependency... and how shall we take care a case when user delete the stage that are dependent on previous stages.

With Thanks !
Newkid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-12 : 01:53:03

"I have tried all the options to retreive the information as suggested by you with SELF JOIN but it fails."
Can you explain a bit more on the fails part ?

"RT_DEPENDENCY = NVARCHAR(5) { In case of multiple dependency it stores information like 2,3,4 etc and RT_RDS_ID is int and RT_STAGE_ORDER is also int"
Can you please post the table DDL, more sample data of different scenario and what do you expect in the result set ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 07:59:03

Yes, and please don't just print out the sample data like your first post. Please provide the "create table" statement as well as the "insert" statements to populate it.
thanks :)

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 08:04:47

I think he meant that what if StageID = 204?
Two levels deep...

Peter Larsson
Helsingborg, Sweden

Newkid
Starting Member

7 Posts

Posted - 2006-09-13 : 00:28:31

Thanks Guys for the input. Your request is my command..

R_Delivery Template

Column Name Data Type Nullable
RDT_ID int No
RDT_RO_ID int No
RDT_PARTNER_RO_ID int No
RDT_RS_ID int No
RDT_RSI_ID int No
RDT_RDS_ID int No
RDT_STAGE_ORDER int Yes
RDT_DEPENDENCY NVARCHAR(50) Yes
RDT_DISABLE_STATE NVARCHAR(1) No
RDT_AUDIT_INFO NVARCHAR(75) No
RDT_AUDIT_NOTE NVARCHAR(100) Yes
RDT_CREATED_DATE DATE Yes
RDT_VERSION int Yes

This table contains template for a single service. Both parties template is stores in the above table.

R_Template

Column Name Data Type Nullable
RT_ID INT No
RT_STAGE_RSI_ID INT No
RT_RDS_ID INT No
RT_STAGE_ORDER INT No
RT_DEPENDENCY NVARCHAR(50) Yes
RT_REC_INTERVAL INT Yes
RT_DISABLE_STATE CHAR(1) Yes
RT_AUDIT_INFO NVARCHAR(100) No
RT_AUDIT_NOTE NVARCHAR(100) Yes
RT_CREATED_DATE DATE Yes
RT_ACTION_BY NVARCHAR(10) Yes
RT_RTI_ID INT Yes
RT_VERSION INT Yes

R_Temlate_Info

RTI_ID INT No
RTI_RO_ID INT Yes
RTI_PARTNER_RO_ID INT Yes
RTI_RS_ID INT Yes
RTI_TEMPLATE_RSI_ID INT Yes
RTI_DISABLE_STATE CHAR(1) Yes
RTI_AUDIT_INFO NVARCHAR(100) Yes
RTI_AUDIT_NOTE VARCHAR2(100) Yes
RTI_CREATED_DATE DATE Yes
RTI_DIR CHAR(5) No
RTI_VERSION INT Yes

I'm extracting the information from the very first table and storing it in the second and third table after merging the records. Couple of other things are there like the Precendence level among the records etc has already been taken care and now merged records are placed in the second and third table respectively. The last two tables has a parent child relation ship.

After merging the records I'm displaying it and both parties has an options to add or delete the records and change the dependencies.. Add options has provided to the user who hasn't have that particular stage but his partner have it and vice versa in case of deletion. If the user adds in between, I need to change the stage order as well as dependencies. In every case dependencies should be calculated dynamically.. That is part of the things which I have to do..

For the same, I was asking how to know which stage is dependent on which stage id as I'm not able to re-calculate the dependencies every time based upon the stage order and precedence level..

Hope, I have explained my requirement.. If any one has any queries please feel free to ask..

Thanks again for your time and input.

With Thanks !
Newkid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 00:34:46

quote:
Originally posted by Newkid

Thanks Guys for the input. Your request is my command..

Seems to be some mismatch what is requested and and what is provided

quote:
Originally posted by TG

Yes, and please don't just print out the sample data like your first post. Please provide the "create table" statement as well as the "insert" statements to populate it.
thanks :)

Be One with the Optimizer
TG

Newkid
Starting Member

7 Posts

Posted - 2006-09-13 : 02:56:17

Certainly KH.. :) Please find it below:

CREATE TABLE R_DELIVERY_TEMPLATE
( RDT_ID INT NOT NULL ENABLE,
RDT_RO_ID INT NOT NULL ENABLE,
RDT_PARTNER_RO_ID INT NOT NULL ENABLE,
RDT_RS_ID INT NOT NULL ENABLE,
RDT_RSI_ID INT NOT NULL ENABLE,
RDT_RDS_ID INT NOT NULL ENABLE,
RDT_STAGE_ORDER INT,
RDT_DEPENDENCY NVARCHAR(50),
RDT_DISABLE_STATE CHAR(1) NOT NULL ENABLE,
RDT_AUDIT_INFO NVARCHAR(75) NOT NULL ENABLE,
RDT_AUDIT_NOTE NVARCHAR(100),
RDT_CREATED_DATE DATE,
RDT_VERSION INT,
PRIMARY KEY (RDT_ID) ENABLE,
CONSTRAINT R_100 FOREIGN KEY (RDT_RSI_ID)
REFERENCES R_STATUS_INFO (RSI_ID) ENABLE,
CONSTRAINT R_101 FOREIGN KEY (RDT_RDS_ID)
REFERENCES R_DELIVERY_STAGE (RDS_ID) ENABLE,
CONSTRAINT R_97 FOREIGN KEY (RDT_RO_ID)
REFERENCES R_OPERATOR (RO_ID) ENABLE,
CONSTRAINT R_98 FOREIGN KEY (RDT_PARTNER_RO_ID)
REFERENCES R_OPERATOR (RO_ID) ENABLE,
CONSTRAINT R_99 FOREIGN KEY (RDT_RS_ID)
REFERENCES R_SERVICES (RS_ID) ENABLE
) ;

CREATE TABLE R_DELIVERY_STAGE
( RDS_ID INT NOT NULL ENABLE,
RDS_RO_ID INT NOT NULL ENABLE,
RDS_NAME NVARCHAR(50) NOT NULL ENABLE,
RDS_REC_INTERVAL INT NOT NULL ENABLE,
RDS_ACTION_BY NVARCHAR(10) NOT NULL ENABLE,
RDS_DISABLE_STATE CHAR(1) NOT NULL ENABLE,
RDS_AUDIT_INFO NVARCHAR(75),
RDS_AUDIT_NOTE NVARCHAR(100),
RDS_CREATED_DATE DATE,
PRIMARY KEY (RDS_ID) ENABLE,
CONSTRAINT R_96 FOREIGN KEY (RDS_RO_ID)
REFERENCES R_OPERATOR (RO_ID) ENABLE
) ;

CREATE TABLE R_TEMPLATE
( RT_ID INT NOT NULL ENABLE,
RT_STAGE_RSI_ID INT NOT NULL ENABLE,
RT_RDS_ID INT NOT NULL ENABLE,
RT_STAGE_ORDER INT NOT NULL ENABLE,
RT_DEPENDENCY NVARCHAR(50),
RT_REC_INTERVAL INT,
RT_DISABLE_STATE CHAR(1),
RT_AUDIT_INFO NVARCHAR(100) NOT NULL ENABLE,
RT_AUDIT_NOTE NVARCHAR(100),
RT_CREATED_DATE DATE,
RT_ACTION_BY NVARCHAR(10),
RT_RTI_ID INT,
RT_VERSION INT,
CONSTRAINT R_112 FOREIGN KEY (RT_STAGE_RSI_ID)
REFERENCES R_STATUS_INFO (RSI_ID) ENABLE,
CONSTRAINT R_113 FOREIGN KEY (RT_RDS_ID)
REFERENCES R_DELIVERY_STAGE (RDS_ID) ENABLE
) ;

CREATE TABLE R_TEMPLATE_INFO
( RTI_ID INT NOT NULL ENABLE,
RTI_RO_ID INT,
RTI_PARTNER_RO_ID INT,
RTI_RS_ID INT,
RTI_TEMPLATE_RSI_ID INT,
RTI_DISABLE_STATE CHAR(1),
RTI_AUDIT_INFO NVARCHAR(100),
RTI_AUDIT_NOTE NVARCHAR(100),
RTI_CREATED_DATE DATE,
RTI_DIR CHAR(5) NOT NULL ENABLE,
RTI_VERSION INT,
PRIMARY KEY (RTI_ID) ENABLE,
CONSTRAINT R_114 FOREIGN KEY (RTI_RO_ID)
REFERENCES R_OPERATOR (RO_ID) ENABLE,
CONSTRAINT R_116 FOREIGN KEY (RTI_RS_ID)
REFERENCES R_SERVICES (RS_ID) ENABLE,
CONSTRAINT R_119 FOREIGN KEY (RTI_TEMPLATE_RSI_ID)
REFERENCES R_STATUS_INFO (RSI_ID) ENABLE
) ;

-- INSERTING into R_TEMPLATE_INFO

Insert into R_TEMPLATE_INFO ("RTI_ID","RTI_RO_ID","RTI_PARTNER_RO_ID","RTI_RS_ID","RTI_TEMPLATE_RSI_ID","RTI_DISABLE_STATE","RTI_AUDIT_INFO","RTI_AUDIT_NOTE","RTI_CREATED_DATE","RTI_DIR","RTI_VERSION") values ('527','21','2','1','100','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN ','5');
Insert into R_TEMPLATE_INFO ("RTI_ID","RTI_RO_ID","RTI_PARTNER_RO_ID","RTI_RS_ID","RTI_TEMPLATE_RSI_ID","RTI_DISABLE_STATE","RTI_AUDIT_INFO","RTI_AUDIT_NOTE","RTI_CREATED_DATE","RTI_DIR","RTI_VERSION") values ('528','2','21','1','100','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN ','6');

-- INSERTING into R_TEMPLATE

Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5131','1','242','3',null,'5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5132','1','243','4','3','5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5133','1','244','5','4','5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5134','1','245','6',null,'1','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5135','1','249','7','2','5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5129','1','257','1',null,'2','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5130','1','241','2',null,'5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','527','5');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5136','1','266','1',null,'1','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5137','1','241','2',null,'5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5138','1','242','3',null,'5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5139','1','243','4','2','5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5140','1','244','5','3','5','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5141','1','245','6','4','1','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5142','1','248','7','5','1','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'VPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5143','1','274','8',null,'20','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');
Insert into R_TEMPLATE ("RT_ID","RT_STAGE_RSI_ID","RT_RDS_ID","RT_STAGE_ORDER","RT_DEPENDENCY","RT_REC_INTERVAL","RT_DISABLE_STATE","RT_AUDIT_INFO","RT_AUDIT_NOTE","RT_CREATED_DATE","RT_ACTION_BY","RT_RTI_ID","RT_VERSION") values ('5144','1','264','9',null,'1','1','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)',null,('09/13/06'),'HPMN','528','6');

I have provided the other two tables for just informations. No data is required for the same as I have to play with two tables i.e. R_Template and R_Template_info.

Similary, I have not provided the foreign keys table. If you need the same let me know..

Hope this is what you all are expecting..

With Thanks !
Newkid

Newkid
Starting Member

7 Posts

Posted - 2006-09-14 : 03:30:11

Any helping hand...

With Thanks !
Newkid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources