ahadeveloper777 ahadeveloper777 - 2 months ago 7
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.

Answer

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