I need to show related record id based document no as reference in same table. I try many time and direction but cannot get a right output.
This below are table and data:
Basically Record ID no 2,4 and 10 are related based on Reference Document No. For example if I select record id no 4 I still can list all related document from first to last transaction.
I hope someone who cross this problem or anybody have a solution from SQL Statement or coding on .net please as long I can show this result.
I assume you're using postgres, since this is tagged with it. You can make a recursive query that will accomplish what you want when you're querying for a given docno:
WITH RECURSIVE t AS ( SELECT docno, refdocno FROM <table> WHERE docno = 'T0003' UNION SELECT blah.docno, blah.refdocno FROM <table> JOIN t ON t.docno = blah.refdocno OR t.refdocno = blah.docno ) SELECT * FROM t;
Note: you'll have to put the docno you're searching for in the with statement. If you need other columns, you can put them in there as well.
PS. I assume that row 10's refdocno was supposed to be T0003 in your example