Skip to main content
Database Administrators

Return to Question

added 644 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.2k
  • 30
  • 423
  • 663

from From last id, recursively find parent id and fill column // SQL Server 2016

Actual structure of table and expected result

CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
) 

INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('A3DEBD5A-C8DD-494E-B2CC-97D693677071','70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('CBC648CE-B98C-4831-B6E2-FBF437BD012B','A3DEBD5A-C8DD-494E-B2CC-97D693677071','CA4462D3-BD2C-4C80-A50E-586DA28A877A');

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table. What i

What I want to do is  :

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with it's occurenceits occurrence in COL1  (step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the columcolumn 'Expected Result' for each row concerned by this ID.

I have tried some self join-joins on the table but i'mI'm very far from the expected result. Any Any idea of how this can be achieved  ?

from last id, recursively find parent id and fill column // SQL Server 2016

Actual structure of table and expected result

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table. What i want to do is  :

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with it's occurence in COL1(step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the colum 'Expected Result' for each row concerned by this ID.

I have tried some self join on the table but i'm very far from the expected result. Any idea of how this can be achieved  ?

From last id, recursively find parent id and fill column

Actual structure of table and expected result

CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
) 

INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('A3DEBD5A-C8DD-494E-B2CC-97D693677071','70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A'),
('CBC648CE-B98C-4831-B6E2-FBF437BD012B','A3DEBD5A-C8DD-494E-B2CC-97D693677071','CA4462D3-BD2C-4C80-A50E-586DA28A877A');

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table.

What I want to do is:

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with its occurrence in COL1  (step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the column 'Expected Result' for each row concerned by this ID.

I have tried some self-joins on the table but I'm very far from the expected result. Any idea of how this can be achieved?

edited title
Link
JCP
JCP
  • 33
  • 5

from last id, recursively find parent id and fill column // SQL Server 2016

added 32 characters in body
Source Link
JCP
JCP
  • 33
  • 5

Actual structure of table and expected result

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table. What i want to do is :

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with it's occurence in COL1(step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the colum 'Expected Result' for each row concerned by this ID.

I have tried some self join on the table but i'm very far from the expected result. Any idea of how this can be achieved ?

Actual structure of table and expected result

Above is the structure of my table. What i want to do is :

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with it's occurence in COL1(step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the colum 'Expected Result' for each row concerned by this ID.

I have tried some self join on the table but i'm very far from the expected result. Any idea of how this can be achieved ?

Actual structure of table and expected result

https://dbfiddle.uk/a-EAQ5YV

Above is the structure of my table. What i want to do is :

  1. From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with it's occurence in COL1(step2)
  2. When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match.
  3. Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the colum 'Expected Result' for each row concerned by this ID.

I have tried some self join on the table but i'm very far from the expected result. Any idea of how this can be achieved ?

Source Link
JCP
JCP
  • 33
  • 5