2

I am trying to find all the descendants or ancestors for a particular IC/SOC using loops in SQL. I basically need to crate an ancestor view or descendants view which lists all the versions before a given version.

Eg : SOC_test_1.1
       SOC_test_1.1.1
          SOC_test_1.1.1.1
          SOC_test_1.1.1.2
       SOC_test_1.1.2
     SOC_test_1.2
       SOC_test_1.2.1
       SOC_test_1.2.2

Table :

Revision Name   Rev Number
SOC_abc_001     1.1
SOC_abc_002     1.2
SOC_abc_003     1.1.1
SOC_abc_004     1.1.2
SOC_abc_005     1.3
SOC_abc_006     1.2.1
SOC_abc_007     1.1.1.1
SOC_abc_008     1.1.1.2
SOC_abc_009     1.4
SOC_abc_010     1.2.1
SOC_abc_011     1.5

I have a table which has revisions and revision numbers , they need not always co-relate to each other.

  • Case 1 : Given SOC_Test_1.1.1.1 I need the table to list all versions above it.

  • Case 2: Given SOC_Test_1.2 it should list SOC_test_1.1

Does this need Stored procedure? Any helpful pointers is appreciated.

Parfait
  • 104,375
  • 17
  • 94
  • 125
Mandara31
  • 89
  • 13
  • is there any way we can get the actual table and data, there are several options including what you are thinking of doing but more information would be helpful – ttallierchio Aug 17 '17 at 20:35
  • I cant share the original table or the data. I could share a screenshot but Stackflow doesnt let me since I am a beginner. – Mandara31 Aug 17 '17 at 20:47
  • screenshots work, or sqlfiddle – ttallierchio Aug 17 '17 at 20:49
  • Says I need to be repuation 10 to be able to add images ! – Mandara31 Aug 17 '17 at 20:53
  • You can do it with [Common table expressions](https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx), here's an [example](https://stackoverflow.com/questions/23019384/how-to-prevent-a-self-referencing-table-from-becoming-circula) of a constraint preventing self referencing entries that uses CTE – Vlad Aug 18 '17 at 02:17

0 Answers0