StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to eliminate duplicate rows joining on tables across databases?
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload