get all parent records from child record

Question

I have an oracle table similar to this

TableName:Note

NoteID(PK)        NoteText    ParentNoteID
1                 x           - 
2                 y           1
3                 z           2

Here I need to perform an like query on top of notetext say, note.note_text like '%z%'which pulls out an third record alone.

I wish to pull the records of it's parent's as well.i.e after getting the third record,which matches through like query i need to travel back by seeing parentNoteID .So parent note of 3 rd record is 2 and parent record of 2 is 1.so the result should fetch all the three record.

To extend this,if my like query matches two child records,apparently it should get all parents records of both.

Example:

NoteID(PK)  NoteText    ParentNoteID
1           x           -  
2           y           1
3           z           2
4           a           -
5           b           4
6           z           5

note.note_text like '%z%' should pull,all the above 6 records.

Thanks in advance Eswar.

Answer 1

you'd use a connect by to do this.

for example something like

select *
  from tbl1
 start with note_text like '%z%'
 connect by note_id = prior parent_note_id

would do it.

example fiddle: http://sqlfiddle.com/#!4/0c6ea/1