{"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":"<p>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<p>Let&#8217;s take this sql for example:<\/p>\n<pre>\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>\n<p>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. <\/p>\n<p>After run demo against this SQL query, we can get the result:<\/p>\n<pre>\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>\n<p>Download source code of this demo: <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/joinRelationAnalyze\/joinRelationAnalyze.zip\">C# version<\/a>, <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/joinRelationAnalyze\/joinRelationAnalyze.java\">Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;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 != &#8217;02&#8217;) AS PSN_CNT, (SELECT count() FROM GRAASPST t WHERE t.ASSMT_NO = a.ASSMT_NO AND t.ROLE != &#8217;02&#8217; AND ASSMT_FIN_YN = &#8216;Y&#8217;) 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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"How to get table\/column relation by analyzing sql join condition\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"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\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"How to get table\/column relation by analyzing sql join condition\" \/>\n<meta property=\"og:url\" content=\"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\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2013-10-02T02:46:57+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"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\/\",\"url\":\"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\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2012-02-13T02:57:32+00:00\",\"dateModified\":\"2013-10-02T02:46:57+00:00\",\"description\":\"How to get table\/column relation by analyzing sql join condition\",\"breadcrumb\":{\"@id\":\"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\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"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\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"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\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How to get table\/column relation by analyzing sql join condition\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"How to get table\/column relation by analyzing sql join condition","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"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\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"How to get table\/column relation by analyzing sql join condition","og_url":"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\/","og_site_name":"SQL and Data Blog","article_modified_time":"2013-10-02T02:46:57+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"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\/","url":"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\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2012-02-13T02:57:32+00:00","dateModified":"2013-10-02T02:46:57+00:00","description":"How to get table\/column relation by analyzing sql join condition","breadcrumb":{"@id":"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\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["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\/"]}]},{"@type":"BreadcrumbList","@id":"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\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"How to get table\/column relation by analyzing sql join condition"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1326"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1326"}],"version-history":[{"count":10,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1326\/revisions"}],"predecessor-version":[{"id":1615,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1326\/revisions\/1615"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}