title: How to eliminate duplicate rows joining on tables across databases?
body: <p>I have been working on this script for a while and have reached a dead end.  The script works but unfortunately produces duplicates.  My script joins two different tables across databases on a state_issue_teacher_id key and then produces the output.  I have checked both tables and the row counts are the same and the join should match records perfectly but evidently there is a problem with my key or the way I'm joining to the table and my output is coming back partially incorrect.  I've also tried concatenating attributes to make a unique key and join the tables but still producing incorrect results.</p>
  
  <p>Here is my script:</p>
  
  <pre><code>SELECT     
         LTRIM(RTRIM(rt.year_time)) AS 'year_time' ,
         LTRIM(RTRIM(rt.state_issue_teacher_id)) AS state_issue_teacher_id ,
         LTRIM(RTRIM(rt.district_code)) AS district_code ,
         rt.district_name ,
         rt.school_name ,
         LTRIM(RTRIM(rt.assignment_code)) AS assignment_code ,
         rt.assignment_desc ,
         LTRIM(RTRIM(rt.position_code)) AS position_code ,
         rt.position_desc ,
         LTRIM(RTRIM(rt.last_name)) AS last_name ,
         LTRIM(RTRIM(rt.first_name)) AS first_name ,
         LTRIM(RTRIM(rt.total_salary)) AS total_salary ,
         rt.assign_fte ,
         LTRIM(RTRIM(rt.school_code)) AS school_code ,
         rt.fte
  
      FROM    staging.dbo.rt AS rt
  
      LEFT JOIN ( SELECT   LTRIM(RTRIM(dti.year)) AS year ,
                      LTRIM(RTRIM(dt.teacher_id)) AS teacher_id ,
                      LTRIM(RTRIM(db.district_code)) AS district_code ,
                      db.district_name ,
                      LTRIM(RTRIM(dt.last_name)) AS last_name ,
                      LTRIM(RTRIM(dt.first_name)) AS first_name ,
                      LTRIM(RTRIM(da.assignment_code)) AS assignment_code ,
                      LTRIM(RTRIM(dp.position_code)) AS position_code ,
                      dre.race_ethnicity_code ,
                      LTRIM(RTRIM(SUBSTRING(db.school_code,10,4))) AS school_code ,
                      da.assignment_desc ,
                      dp.position_desc ,
                      fs.total_fte
  
             FROM     mart.dbo.fact_s AS fs
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_building
                      AS db ON fs.building_key = db.building_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_teacher
                      AS dt ON fs.teacher_key = dt.teacher_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_assignment
                      AS da ON fs.assignment_key = da.assignment_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_race_ethnicity
                      AS dre ON dt.race_ethnicity_key = dre.race_ethnicity_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_gender
                      AS dg ON dt.gender_key = dg.gender_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_time
                      AS dti ON fs.time_key = dti.time_key
                      LEFT OUTER JOIN mart.dbo.fact_s.dbo.dim_position
                      AS dp ON fs.position_key = dp.position_key
             WHERE    dti.year = '2012'
  
  
  
           ) raw ON    rt.state_issue_teacher_id = raw.teacher_id                 
                          AND rt.year_time = raw.year 
                          AND rt.last_name = raw.last_name 
                          AND rt.first_name = raw.first_name 
                          AND rt.district_code = raw.district_code
                          AND rt.position_code = raw.position_code
                          AND rt.school_code = RAW.school_code
                          AND rt.assignment_code = raw.assignment_code
  
  
  
      WHERE   rt.year_time = '2012'
  
  
  
      ORDER BY rt.last_name, rt.first_name
  </code></pre>
  
  <p>The output that I'm getting is:
  <img src="https://i.stack.imgur.com/5JhnM.gif" alt="enter image description here"></p>
  
  <p>The fte for the combined teachers assignments should add up to 1.  But teachers that have the same assignment_code/desc with multiple partial assignments are producing duplicates. Example: Jane Doe appears 4 times with a total fte of 2.0 instead of 2 times with the correct total of 1.0.  The output should read as follows.
  <img src="https://i.stack.imgur.com/oVLLj.gif" alt="enter image description here"></p>