Skip to main content
Database Administrators

Return to Question

formatting
Source Link
Charlieface
Charlieface
  • 14.5k
  • 14
  • 40

I used to think I was good at SQL until I received this latest request at work. WeWe have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time. I

I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID. There

There are three different types of cases that need to be solved. A) Simple case of A->B->C. In the view, all three of these records would receive the same GroupID B) Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation. C) A DefectID has two or more PreviousDefectIDs (comma seprated)

  1. Simple case of A->B->C. In the view, all three of these records would receive the same GroupID
  2. Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation.
  3. A DefectID has two or more PreviousDefectIDs (comma seprated)

Note, there are more records in the resulting view than in the table, because when a single DefectIDDefectID leads to multiple other DefectIDsDefectIDs, there is a new branch created for each chain and each gets their own GroupIDGroupID number.

I used to think I was good at SQL until I received this latest request at work. We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time. I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID. There are three different types of cases that need to be solved. A) Simple case of A->B->C. In the view, all three of these records would receive the same GroupID B) Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation. C) A DefectID has two or more PreviousDefectIDs (comma seprated)

Note, there are more records in the resulting view than in the table, because when a single DefectID leads to multiple other DefectIDs, there is a new branch created for each chain and each gets their own GroupID number.

We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.

I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID.

There are three different types of cases that need to be solved.

  1. Simple case of A->B->C. In the view, all three of these records would receive the same GroupID
  2. Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation.
  3. A DefectID has two or more PreviousDefectIDs (comma seprated)

Note, there are more records in the resulting view than in the table, because when a single DefectID leads to multiple other DefectIDs, there is a new branch created for each chain and each gets their own GroupID number.

deleted 16 characters in body
Source Link
MM3000
MM3000
  • 11
  • 2
CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
       ('B', 'A'),
       ('C', 'B'),
       ('D', NULL),
       ('E', 'D'),
       ('F', 'D'),previousdefectid
       ('G', 'D'),
       ('H', 'E'),
       ('I', 'E'),
       ('K', NULL),
       ('L', NULL),
       ('M', NULL),
       ('N', 'L,M'),
       ('O', 'K,N') 

CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
       ('B', 'A'),
       ('C', 'B'),
       ('D', NULL),
       ('E', 'D'),
       ('F', 'D'),previousdefectid
       ('G', 'D'),
       ('H', 'E'),
       ('I', 'E'),
       ('K', NULL),
       ('L', NULL),
       ('M', NULL),
       ('N', 'L,M'),
       ('O', 'K,N') 

CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
       ('B', 'A'),
       ('C', 'B'),
       ('D', NULL),
       ('E', 'D'),
       ('F', 'D'),
       ('G', 'D'),
       ('H', 'E'),
       ('I', 'E'),
       ('K', NULL),
       ('L', NULL),
       ('M', NULL),
       ('N', 'L,M'),
       ('O', 'K,N')
Source Link
MM3000
MM3000
  • 11
  • 2

How to write query to link complex Parent-Child relationships

I used to think I was good at SQL until I received this latest request at work. We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time. I need to link these fields together by creating a view that will have a third column, maybe called GroupID, so that each chain will receive its own GroupID. There are three different types of cases that need to be solved. A) Simple case of A->B->C. In the view, all three of these records would receive the same GroupID B) Two or more DefectIDs come from the same PreviousDefectID. In this case, the view would have a different GroupID for each new branch that is created. This can keep happening in more than one generation. C) A DefectID has two or more PreviousDefectIDs (comma seprated)

So, the following table:

CREATE TABLE #Temp (
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Temp (ID, PreviousID)
VALUES ('A', NULL),
       ('B', 'A'),
       ('C', 'B'),
       ('D', NULL),
       ('E', 'D'),
       ('F', 'D'),previousdefectid
       ('G', 'D'),
       ('H', 'E'),
       ('I', 'E'),
       ('K', NULL),
       ('L', NULL),
       ('M', NULL),
       ('N', 'L,M'),
       ('O', 'K,N')

Would result in a view that looks like the following table:

CREATE TABLE #Final (
GroupID INT,
ID VARCHAR(50),
PreviousID VARCHAR(50))

INSERT INTO #Final (GroupID, ID, PreviousID)
VALUES (1, 'A', NULL),
       (1, 'B', 'A'),
       (1, 'C', 'B'),
       (2, 'D', NULL),
       (3, 'D', NULL),
       (4, 'D', NULL),
       (2, 'E', 'D'),
       (3, 'F', 'D'),
       (4, 'G', 'D'),
       (7, 'D', NULL),
       (7, 'E', 'D'),
       (7, 'H', 'E'),
       (7, 'I', 'E'),
       (5, 'K', NULL),
       (5, 'L', NULL),
       (5, 'M', NULL),
       (5, 'N', 'L,M'),
       (5, 'O', 'K,N')

Note, there are more records in the resulting view than in the table, because when a single DefectID leads to multiple other DefectIDs, there is a new branch created for each chain and each gets their own GroupID number.

I know this is a complicated question, any help at all would be appreciated.

I am attaching what I have so far, but it isn't quite correct, and doesn't yet address the third type of situation.

select ID,
PreviousID, count(PreviousID) over (partition by ID, PreviousID) as 'cnt', null
from #temp temp
union all
(
select temp.ID, temp.PreviousID, null, row_number () over (partition by temp.ID, temp.PreviousID order by temp.PreviousID) as 'rn'
from #temp temp inner join #temp temp1 on temp.ID = temp1.PreviousID
)

order by 1