August 18, 2015 at 5:15 am
Hi,
Sample data to play with,
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 union all
select 'Process5',4,5;
declare @Steps table(StepId int identity(1,1) primary key,StepName varchar(100),ParentStep int);
Insert into @Steps(StepName,ParentStep)
select 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
select 'step2',1 union all
select 'step3',1 union all
select 'step4', 0 union all -- Parent step....
select 'step5',4 union all
select 'step6',4 union all
select 'step7',4 ;
In we see the @Process table data, third row and fifth row are wrong. At third row,6 is not child of parent 1 in (Steps table). Likewise child 2 is not parent of 4 in (steps table).
So basically i need to get the process whose parent mapped with wrong child. below the output format that i need to generate,
Select 'Process3','Step1' as Parent,'Step6' as child union all
Select 'Process4','Step4' as Parent,'Step2' as child;
[this is sample table and data/ not my actual table. but the structure of this table is same]. Please help me on achieving the logic and any best sample query achieve this.
thanks
August 18, 2015 at 5:39 am
born2achieve (8/18/2015)
Hi,Sample data to play with,
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 union all
select 'Process5',4,5;
declare @Steps table(StepId int identity(1,1) primary key,StepName varchar(100),ParentStep int);
Insert into @Steps(StepName,ParentStep)
select 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
select 'step2',1 union all
select 'step3',1 union all
select 'step4', 0 union all -- Parent step....
select 'step5',4 union all
select 'step6',4 union all
select 'step7',4 ;
In we see the @Process table data, third row and fifth row are wrong. At third row,6 is not child of parent 1 in (Steps table). Likewise child 2 is not parent of 4 in (steps table).
So basically i need to get the process whose parent mapped with wrong child. below the output format that i need to generate,
Select 'Process3','Step1' as Parent,'Step6' as child union all
Select 'Process4','Step4' as Parent,'Step2' as child;
[this is sample table and data/ not my actual table. but the structure of this table is same]. Please help me on achieving the logic and any best sample query achieve this.
thanks
What are the two tables? Is @Process an output from a query attempt or a source table which happens to be wrong? If @Process is an output set, then where does the text 'Process3' originate from?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 5:50 am
Hi Chris,
Thanks for your reply and @Process and at @steps are two tables.
select ProcessName,S.Stepname as parent,S.stepname as child from @Process P join @steps S.......
any suggestions and sample please
August 18, 2015 at 5:51 am
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 union all
select 'Process5',4,5;
declare @Steps table(StepId int identity(1,1) primary key,StepName varchar(100),ParentStep int);
Insert into @Steps(StepName,ParentStep)
select 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
select 'step2',1 union all
select 'step3',1 union all
select 'step4', 0 union all -- Parent step....
select 'step5',4 union all
select 'step6',4 union all
select 'step7',4 ;
SELECT
P.ProcessName AS ProcessName
,PS.StepName AS Parent
,S.StepName AS Child
FROM @Process P
CROSS APPLY @Steps S
CROSS APPLY @Steps PS
WHERE P.ChildStep = S.StepId
AND P.ParentStep <> S.ParentStep
AND P.ParentStep = PS.StepId;
Results
ProcessName Parent Child
------------ -------- -------
Process3 Step1 step6
Process4 step4 step2
August 18, 2015 at 5:56 am
born2achieve (8/18/2015)
Hi Chris,Thanks for your reply and @Process and at @steps are two tables.
select ProcessName,S.Stepname as parent,S.stepname as child from @Process P join @steps S.......
any suggestions and sample please
In we see the @Process table data, third row and fifth row are wrong. At third row,6 is not child of parent 1 in (Steps table). Likewise child 2 is not parent of 4 in (steps table).
So why don't you correct the data in @Process? Is this what you are asking us to do?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 6:03 am
Hi Chris,
Sorry, Am not sure whats wrong in the test data. Please help me on understanding what wrong in test data.
Hi Eirik,
Thanks for your sample.
August 18, 2015 at 6:08 am
born2achieve (8/18/2015)
Hi Chris,Sorry, Am not sure whats wrong in the test data. Please help me on understanding what wrong in test data.
Hi Eirik,
Thanks for your sample.
You say "@Process table data, third row and fifth row are wrong."
So is it correct, or not? If it isn't, then why don't you correct it?
Or is this what you want us to do?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2015 at 8:18 am
opps. Sorry for the confusion. I meant to say, those two records should be displayed in the output because it was wrongly mapped.
August 18, 2015 at 1:30 pm
Eirikur Eiriksson (8/18/2015)
Quick suggestion😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 union all
select 'Process5',4,5;
declare @Steps table(StepId int identity(1,1) primary key,StepName varchar(100),ParentStep int);
Insert into @Steps(StepName,ParentStep)
select 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
select 'step2',1 union all
select 'step3',1 union all
select 'step4', 0 union all -- Parent step....
select 'step5',4 union all
select 'step6',4 union all
select 'step7',4 ;
SELECT
P.ProcessName AS ProcessName
,PS.StepName AS Parent
,S.StepName AS Child
FROM @Process P
CROSS APPLY @Steps S
CROSS APPLY @Steps PS
WHERE P.ChildStep = S.StepId
AND P.ParentStep <> S.ParentStep
AND P.ParentStep = PS.StepId;
Results
ProcessName Parent Child
------------ -------- -------
Process3 Step1 step6
Process4 step4 step2
Why do you use a CROSS APPLY here? You can do the same thing with an INNER JOIN, and I think that it's easier to follow.
SELECT
P.ProcessName AS ProcessName
,PS.StepName AS Parent
,S.StepName AS Child
FROM @Process P
INNER JOIN @Steps S
ON P.ChildStep = S.StepId
INNER JOIN @Steps PS
ON P.ParentStep = PS.StepId
WHERE P.ParentStep <> S.ParentStep;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2015 at 2:38 pm
born2achieve (8/18/2015)
Hi,Sample data to play with,
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 union all
select 'Process5',4,5;
declare @Steps table(StepId int identity(1,1) primary key,StepName varchar(100),ParentStep int);
Insert into @Steps(StepName,ParentStep)
select 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
select 'step2',1 union all
select 'step3',1 union all
select 'step4', 0 union all -- Parent step....
select 'step5',4 union all
select 'step6',4 union all
select 'step7',4 ;
In we see the @Process table data, third row and fifth row are wrong. At third row,6 is not child of parent 1 in (Steps table). Likewise child 2 is not parent of 4 in (steps table).
So basically i need to get the process whose parent mapped with wrong child. below the output format that i need to generate,
Select 'Process3','Step1' as Parent,'Step6' as child union all
Select 'Process4','Step4' as Parent,'Step2' as child;
[this is sample table and data/ not my actual table. but the structure of this table is same]. Please help me on achieving the logic and any best sample query achieve this.
thanks
It appears that what's needed is a query that detects differences between the Steps table and the Processes table. How about this rather simplistic one?
DECLARE @Process AS TABLE (
ProcessId int IDENTITY(1,1) PRIMARY KEY,
ProcessName varchar(100),
ParentStep int,
ChildStep int
);
INSERT INTO @Process(ProcessName,ParentStep,ChildStep)
SELECT 'Process1',1,2 union all
SELECT 'Process2',1,3 union all
SELECT 'Process3',1,6 union all
SELECT 'Process4',4,2 union all
SELECT 'Process5',4,5;
declare @Steps AS table(
StepId int IDENTITY(1,1) PRIMARY KEY,
StepName varchar(100),
ParentStep int
);
INSERT INTO @Steps(StepName,ParentStep)
SELECT 'Step1',0 union all --- Parent step(StepId is 1 and it is auto generated id)
SELECT 'step2',1 union all
SELECT 'step3',1 union all
SELECT 'step4', 0 union all -- Parent step....
SELECT 'step5',4 union all
SELECT 'step6',4 union all
SELECT 'step7',4 ;
SELECT ParentStep, ChildStep
FROM @Process
EXCEPT
SELECT ParentStep, StepId
FROM @Steps
WHERE ParentStep <> 0;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 18, 2015 at 2:44 pm
drew.allen (8/18/2015)
Why do you use a CROSS APPLY here? You can do the same thing with an INNER JOIN, and I think that it's easier to follow.
You are right, there is virtually no difference between the two apart from the column order in the two first scans which makes no difference. Whether it is easier or not to follow I don't know, I cannot see any difference there either. CROSS APPLY is on the other hand slightly quicker to write;-)
😎
August 18, 2015 at 2:50 pm
sgmunson (8/18/2015)
It appears that what's needed is a query that detects differences between the Steps table and the Processes table. How about this rather simplistic one?
Simple solution but not too effective, requires a blocking sort for the left anti semi join and although it only has two scans opposite to the three scans in the other it becomes far more expensive.
😎
August 18, 2015 at 3:22 pm
thank you allen, eirik and sgmunson.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply