How to link two unrelated tables, through one table with common values?

问题: I am adding a commission percent column to an existing query. However, the commission data sits in a table unrelated to the main table (A) used within the query. However, t...

问题:

I am adding a commission percent column to an existing query. However, the commission data sits in a table unrelated to the main table (A) used within the query. However, these two tables have common columns/values with Table B.

I have three tables, A, B and C below

Table A     
Reference   Value_Name  Renewal_Code
1           A           N
2           A           R
3           B           N
4           A           R
4           A           N

Table B     
Reference   Value_Name  Prod_Code
1           A           0016
2           A           0027
4           A           0032
4           A           0032

Table C     
A_Prod_Code      A_Tans_Code    **Commission_Percent**
0016             Renewal        5
0027             Renewal        5
0032             New            10
0032             Renewal        5

I need to get the Commission_Percent from Table C relating to the corresponding Renewal_Code from Table A. This is the same as A_Tans_Code from Table C except that Table C spells out Renewal or New and Table A only uses R or N.

I have been able to pull through the Commission_Percent column into the output by using Table B for common values, but all values show as NULL.

I have also tried using a decode statement in order to link the Renewal_code/A_Trans_Code columns from Tables A and C.

( 
    SELECT 
          distinct c.commision_percent 
    FROM 
          TableA a 

          JOIN TableB b ON a.reference = b.reference 
            AND b.value_name = 'A' 
          JOIN TableC c ON b.prod_code = c.a_prod_code 
            AND b.value_name = 'A' 
          JOIN TableC c  ON a.renewal_code = decode(c.a_trans_code, 'Rewnal','R','New','N')          
  ) Commission_Percent

I need the correct commission_percent for Renewal and New business to come through for each reference. So far, I am only getting NULLs as I am having a hard time linking Tables A and C's Renewal_code and A_prod_code columns.

Any help is greatly appreciated!


回答1:

This creates reproducible testing by cleaning up temp tables and re-inserting the data. And could be a model for other solutions.

The SQL 'Select...' joins to a single TableC that has two parts to the ON condition-- prod_code and renewal_code (instead of two joins). Just remove the "a.*," to use it in your sql. (the decode function was changed to use a subscript of the first char of the A_Trans_Code).

IF OBJECT_ID('tempdb..#TableA') IS NOT NULL DROP TABLE #TableA
GO
CREATE TABLE #TableA
    (     Reference          INTEGER 
        , Value_Name         VARCHAR(10)
        , Renewal_Code       VARCHAR(10)    )

    INSERT INTO #TableA VALUES( 1, 'A', 'N'   );
    INSERT INTO #TableA VALUES( 2, 'A', 'R'   );
    INSERT INTO #TableA VALUES( 3, 'B', 'N'   );
    INSERT INTO #TableA VALUES( 4, 'A', 'R'   );
    INSERT INTO #TableA VALUES( 4, 'A', 'N'   );

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB
GO
CREATE TABLE #TableB
    (     Reference          INTEGER 
        , Value_Name         VARCHAR(10)
        , Prod_Code          VARCHAR(10)    )

    INSERT INTO #TableB VALUES( 1, 'A', '0016'   );
    INSERT INTO #TableB VALUES( 2, 'A', '0027'   );
    INSERT INTO #TableB VALUES( 4, 'A', '0032'   );
    INSERT INTO #TableB VALUES( 4, 'A', '0032'   );

IF OBJECT_ID('tempdb..#TableC') IS NOT NULL DROP TABLE #TableC
GO
CREATE TABLE #TableC
    (     Prod_Code          VARCHAR(10)
        , A_Trans_Code        VARCHAR(10)
        , Commission_Percent INTEGER    )

    INSERT INTO #TableC VALUES( '0016', 'Renewal', 5   );
    INSERT INTO #TableC VALUES( '0027', 'Renewal', 5   );
    INSERT INTO #TableC VALUES( '0032', 'New',     10   );
    INSERT INTO #TableC VALUES( '0032', 'Renewal', 5   );



SELECT distinct a.*,  c.commission_percent 
          FROM #TableA a
          JOIN #TableB b ON a.reference = b.reference 
            AND a.value_name = b.value_name 
          JOIN #TableC c ON b.prod_code = c.prod_code 
           AND a.renewal_code = SUBSTRING(c.a_trans_code,1,1) 

Results are--

Reference   Value_Name  Renewal_Code    commission_percent
2           A           R               5
4           A           N               10
4           A           R               5 

Code to put in your sql

   ( SELECT distinct  c.commission_percent 
              FROM TableA a
              JOIN TableB b ON a.reference = b.reference 
                AND a.value_name = b.value_name 
              JOIN TableC c ON b.prod_code = c.prod_code 
               AND a.renewal_code = SUBSTRING(c.a_trans_code,1,1) 
    ) Commission_Percent

回答2:

 WITH table_a AS (

      SELECT 1 AS reference, 'A' AS value_name, 'N' AS renewal_code FROM DUAL UNION ALL
      SELECT 2 AS reference, 'A' AS value_name, 'R' AS renewal_code FROM DUAL UNION ALL
      SELECT 3 AS reference, 'B' AS value_name, 'N' AS renewal_code FROM DUAL UNION ALL
      SELECT 4 AS reference, 'A' AS value_name, 'R' AS renewal_code FROM DUAL UNION ALL
      SELECT 4 AS reference, 'A' AS value_name, 'N' AS renewal_code FROM DUAL

      ),
      table_b AS (

      SELECT 1 AS reference, 'A' AS value_name, '0016' AS prod_code FROM DUAL UNION ALL
      SELECT 2 AS reference, 'A' AS value_name, '0027' AS prod_code FROM DUAL UNION ALL
 /*     SELECT 4 AS reference, 'A' AS value_name, '0032' AS prod_code FROM DUAL UNION ALL duplicate row excluded */
      SELECT 4 AS reference, 'A' AS value_name, '0032' AS prod_code FROM DUAL

      ),
      table_c AS (

      SELECT '0016' AS a_prod_code, 'Renewal'   AS a_tans_code,  5 AS commission_percent FROM DUAL UNION ALL
      SELECT '0027' AS a_prod_code, 'Renewal'   AS a_tans_code,  5 AS commission_percent FROM DUAL UNION ALL
      SELECT '0032' AS a_prod_code, 'New'       AS a_tans_code, 10 AS commission_percent FROM DUAL UNION ALL
      SELECT '0032' AS a_prod_code, 'Renewal'   AS a_tans_code,  5 AS commission_percent FROM DUAL

      )

          SELECT a.reference AS ref_a,
                 a.value_name AS value_name_a,
                 b.reference AS ref_b,
                 b.value_name AS value_name_b,
                 a.renewal_code,
                 b.prod_code,
                 c.commission_percent
            FROM table_a a
 LEFT OUTER JOIN table_b b
                  ON a.value_name = b.value_name
                 AND a.reference = b.reference
 LEFT OUTER JOIN table_c c
                  ON SUBSTR(c.a_tans_code, 1, 1) = a.renewal_code
                 AND c.a_prod_code = b.prod_code
 ;

The common table expression (CTE) is just to create the same values you posted (CTE is the part using the WITH construct).

Results:

      REF_A VALUE_NAME_A         REF_B VALUE_NAME_B    RENEWAL_CODE    PROD_CODE    COMMISSION_PERCENT
 ---------- --------------- ---------- --------------- --------------- ---------- --------------------
          2 A                        2 A               R               0027                          5
          4 A                        4 A               N               0032                         10
          4 A                        4 A               R               0032                          5
          1 A                        1 A               N               0016                           
          3 B                                          N                                              

You may have to check the values in the rows you posted that do not result in a join. This also assumes there are no codes:code_name relationships that violate the rule of first letter of code name = code.

  • 发表于 2019-03-28 15:57
  • 阅读 ( 161 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除