title: SQL Full join on two columns
body: <p>I'm trying to join two tables but the columns emp_id and scheme_id may be null or not populated in either table but if it is populated in either then I need to return the total pen_ee for that employee for each scheme (further table descriptions below). I can't edit the table structure and have to work with what I have.</p>
  
  <p>I've been trying to use a full join to do this but don't understand if you can do a full join on two fields emp_id &amp; scheme_id to get the required result.</p>
  
  <p><strong>Table PAYAUDPEN</strong><br/>
  This is the first two months of the year.<br/> 
   - Employee A has given 44.06 to scheme BMAL.<br/>
   - Employee B has given 98.06 to scheme BMAL.<br/>
   - Employee B has given 98.06 to scheme CLFL.<br/></p>
  
  <pre><code>emp_id, period_id, scheme_id, pen_ee
  A,      201601,         BMAL, 22.03
  A,      201602,         BMAL, 22.03
  B,      201601,         BMAL, 98.06
  B,      201602,         CLFL, 98.06
  </code></pre>
  
  <p><strong>Table PAYISPEN</strong><br/>
  This is the third &amp; current month of the year. The system always puts the current month into this table)<br/>
  - Employee A gave 22.03.<br/>
  - Employee B gave 98.06.<br/>
   (Note employee B did not contribute to the BMAL scheme again in month 3 which is part of the issue).<br/> </p>
  
  <pre><code>emp_id, scheme_id, pen_ee
  A, BMAL, 22.03
  B, CLFL, 98.06
  </code></pre>
  
  <p><strong>Required Result</strong>
  The SQL statement needs to return the 3 periods added together, for each employee for each scheme that they contributed to.<br/>
  - Employee A would be 44.06 + 22.03=66.09  for scheme BMAL.<br/>
  - Employee B would be 98.06 + NULL =98.06  for scheme BMAL.<br/>
  - Employee B would be 98.06 + 98.06=196.12 for scheme CLFL.<br/></p>
  
  <pre><code>A, BMAL, 66.09  
  B, BMAL, 98.06
  B, CLFL, 196.12
  </code></pre>
  
  <p>To create the basics of the two tables and populate with the example data above run the following queries.</p>
  
  <pre><code>CREATE TABLE [dbo].[payaudpen](
  [emp_id] [char](10) NOT NULL,
  [period_id] [char](6) NOT NULL,
  [scheme_id] [char](10) NOT NULL,
  [pen_ee] [numeric](15, 2) NULL)
  
  CREATE TABLE [dbo].[payispen](
  [emp_id] [char](10) NOT NULL,
  [scheme_id] [char](10) NOT NULL,
  [pen_ee] [numeric](15, 2) NULL )
  
  INSERT INTO payaudpen VALUES ('A','201601','BMAL','22.03'), ('A','201602','BMAL','22.03'), ('B','201601','BMAL','98.06'), ('B','201602','CLFL','98.06')
  INSERT INTO payispen VALUES ('A','BMAL','22.03'), ('B','CLFL','98.06')
  </code></pre>
  
  <p>Current statement that I'm using:</p>
  
  <pre><code>SELECT a.emp_id,
         a.scheme_id,
         SUM(a.pen_ee)+AVG(b.pen_ee)
  FROM payaudpen a 
  FULL JOIN payispen b 
    ON a.emp_id=b.emp_id
  GROUP BY a.scheme_id, a.emp_id
  </code></pre>
  
  <p><strong>Incorrect result</strong><br/>
  Doesn't return the correct value for employee B for each scheme.</p>
  
  <pre><code>A, BMAL, 66.09  
  B, BMAL, 196.12
  B, CLFL, 196.12
  </code></pre>