{"id":1326,"date":"2012-02-12T21:57:32","date_gmt":"2012-02-13T02:57:32","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1326"},"modified":"2013-10-01T21:46:57","modified_gmt":"2013-10-02T02:46:57","slug":"how-to-get-tablecolumn-relation-by-analyzing-sql-join-condition","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/how-to-get-tablecolumn-relation-by-analyzing-sql-join-condition\/","title":{"rendered":"How to get table\/column relation by analyzing sql join condition"},"content":{"rendered":"

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.<\/p>\n

Let’s take this sql for example:<\/p>\n

    \r\nSELECT a.ASSMT_NO,\r\n       b.LINK_PARAM,\r\n       c.EXPL                                               AS LINK_PG,\r\n       (SELECT count()\r\n        FROM   GRAASPST t\r\n        WHERE  t.ASSMT_NO = a.ASSMT_NO\r\n               AND t.ROLE != '02')                          AS PSN_CNT,\r\n       (SELECT count()\r\n        FROM   GRAASPST t\r\n        WHERE  t.ASSMT_NO = a.ASSMT_NO\r\n               AND t.ROLE != '02'\r\n               AND ASSMT_FIN_YN = 'Y')                      AS PSN_FIN_CNT,\r\n       (SELECT Avg(assmt_pts)\r\n        FROM   GRAASSMT t\r\n        WHERE  t.ASSMT_NO = a.ASSMT_NO\r\n               AND t.ASSMT_TGT_SEQ_NO = a.ASSMT_TGT_SEQ_NO) AS ASSMT_PTS_AVG,\r\n       a.ASSMT_RES,\r\n       a.ASSMT_RPT_SUB_TITLE\r\nFROM   GRAASTAT a\r\n       JOIN GRAASRET b\r\n         ON b.DELIB_REQ_NO = a.DELIB_REQ_NO\r\n       JOIN GRTCODDT c\r\n         ON c.DIV_CD = 'GR013'\r\n            AND c.CD = b.DELIB_SLCT\r\n       JOIN CMUSERMT d\r\n         ON d.USERID = a.REGID\r\nWHERE  a.ASSMT_NO = :ASSMT_NO\r\nORDER  BY a.ASSMT_TGT_SEQ_NO \r\n<\/pre>\nBoth 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. <\/p>\nAfter run demo against this SQL query, we can get the result:<\/p>\n\r\nJoinTable1         JoinColumn1\t     JoinTable2\t   JoinColumn2     \r\nGRAASRET\t   DELIB_REQ_NO      GRAASTAT\t   DELIB_REQ_NO    \r\nGRTCODDT\t   CD\t\t     GRAASRET\t   DELIB_SLCT      \r\nCMUSERMT\t   USERID\t     GRAASTAT\t   REGID\t          \r\nGRAASPST\t   ASSMT_NO\t     GRAASTAT\t   ASSMT_NO        \r\nGRAASSMT\t   ASSMT_NO\t     GRAASTAT\t   ASSMT_NO        \r\nGRAASSMT\t   ASSMT_TGT_SEQ_NO  GRAASTAT\t   ASSMT_TGT_SEQ_NO\r\n<\/pre>\nDownload source code of this demo: C# version<\/a>, Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n