Help Needed in Relational Logic

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  August 18, 2015 at 5:15 am

  #303490

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 18, 2015 at 5:39 am

  #1820667

  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?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  August 18, 2015 at 5:50 am

  #1820671

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 18, 2015 at 5:51 am

  #1820672

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 18, 2015 at 5:56 am

  #1820675

  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?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  August 18, 2015 at 6:03 am

  #1820677

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 18, 2015 at 6:08 am

  #1820679

  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?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  August 18, 2015 at 8:18 am

  #1820762

  opps. Sorry for the confusion. I meant to say, those two records should be displayed in the output because it was wrongly mapped.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  August 18, 2015 at 1:30 pm

  #1820877

  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 18, 2015 at 2:38 pm

  #1820890

  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) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 18, 2015 at 2:44 pm

  #1820891

  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;-)

  😎

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 18, 2015 at 2:50 pm

  #1820893

  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.

  😎

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  August 18, 2015 at 3:22 pm

  #1820899

  thank you allen, eirik and sgmunson.