CODEWITHSUNDEEP
sqloracle-databaserecursive-query
wasp256
wasp256

Reputation: 6242

Recursive select statement

I have the following table entries:

 Col1    |    Col2
 ------------------
  3RDT   |  3R9H
  3R9H   |  
  3R9J   |  3RDT
  3R9J   |  3R9H
  3HHA   |  3ABC
  3XXZ   |  3HHA

I have a value 3R9J. Now I want all records where Col1 has that value, in this case record 3 and 4.

Now I take the Col2 values of this records (3RDT and 3R9H) and consider those as my new Col1 values to get all records with the Col1 value 3RDT or 3R9H.

This should be done recursively. In addition it should also select all records in the other direction. If I start with 3RDT for Col1 I get the records where Col2 is 3RDT (3), then I have 3R9J for Col1 and get all records where I have 3R9J as Col1 value.

The expected output data in my example above should be the first 4 records.

Upvotes: 1

Views: 80

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

If I understood your question correctly, that this query will do the job.

SQLFiddle

select *
  from test
  connect by col1 = prior col2
  start with col1 = '3R9J'
union
select *
  from test
  connect by prior col1 = col2
  start with col1 = '3RDT';

Please note that the union causes that each line is shown once.

Upvotes: 1

Related Questions