ahadeveloper777 ahadeveloper777 - 26 days ago 4x
SQL Question

How To Find Related Record ID Based Reference Document No on SQL Statement

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:

Table A

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:

    SELECT docno, refdocno
    FROM <table>
    WHERE docno = 'T0003'


    SELECT blah.docno, blah.refdocno
    FROM <table>
    JOIN t ON t.docno    = blah.refdocno
           OR t.refdocno = blah.docno
  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