Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# How to get table/column relation by analyzing sql join condition

Sometimes you want to discover what columns were used when multi-tables were joined. This is quite useful when you want to design a relationship in ERD by using existing SQL query in a legacy system. General SQL Parser can help you to achieve this automatically and quickly.

Let’s take this sql for example:

    SELECT a.ASSMT_NO,
           b.LINK_PARAM,
           c.EXPL                                               AS LINK_PG,
           (SELECT count()
            FROM   GRAASPST t
            WHERE  t.ASSMT_NO = a.ASSMT_NO
                   AND t.ROLE != '02')                          AS PSN_CNT,
           (SELECT count()
            FROM   GRAASPST t
            WHERE  t.ASSMT_NO = a.ASSMT_NO
                   AND t.ROLE != '02'
                   AND ASSMT_FIN_YN = 'Y')                      AS PSN_FIN_CNT,
           (SELECT Avg(assmt_pts)
            FROM   GRAASSMT t
            WHERE  t.ASSMT_NO = a.ASSMT_NO
                   AND t.ASSMT_TGT_SEQ_NO = a.ASSMT_TGT_SEQ_NO) AS ASSMT_PTS_AVG,
           a.ASSMT_RES,
           a.ASSMT_RPT_SUB_TITLE
    FROM   GRAASTAT a
           JOIN GRAASRET b
             ON b.DELIB_REQ_NO = a.DELIB_REQ_NO
           JOIN GRTCODDT c
             ON c.DIV_CD = 'GR013'
                AND c.CD = b.DELIB_SLCT
           JOIN CMUSERMT d
             ON d.USERID = a.REGID
    WHERE  a.ASSMT_NO = :ASSMT_NO
    ORDER  BY a.ASSMT_TGT_SEQ_NO

Both ansi join syntax and Oracle propriety join syntax were used when join tables in this SQL query, tables in the outer level was joined with tables in the inner level which make it quite time consume to find out table column join relationship manually.

After run demo against this SQL query, we can get the result:

    JoinTable1         JoinColumn1	     JoinTable2	   JoinColumn2
    GRAASRET	   DELIB_REQ_NO      GRAASTAT	   DELIB_REQ_NO
    GRTCODDT	   CD		     GRAASRET	   DELIB_SLCT
    CMUSERMT	   USERID	     GRAASTAT	   REGID
    GRAASPST	   ASSMT_NO	     GRAASTAT	   ASSMT_NO
    GRAASSMT	   ASSMT_NO	     GRAASTAT	   ASSMT_NO
    GRAASSMT	   ASSMT_TGT_SEQ_NO  GRAASTAT	   ASSMT_TGT_SEQ_NO

Download source code of this demo: C# version, Java version