Styjun

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!

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

 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.