Joining data without
losing records
Adding a grade to an attendance report should not quietly remove students—or count their attendance twice.
Try the match. Inspect the result.
Each attendance row belongs to one student for November 2–6, 2026: five possible attendance days. The lookup is intended to supply one eligible enrollment row per student for that same school and period. Student ID is the match key in this simplified example.
Attendance join comparison
Sierra Vista Middle School · Four invented student records · November 2–6, 2026 · Five possible attendance days per student. This is an experiment record, not an approved attendance report.
1. Attendance extract
The starting point stays unchanged.
| Source row | Student ID | Days present |
|---|
Baseline: 4 rows · 4 students · 16 days present
2. Enrollment lookup
| Lookup row | Student ID | Grade |
|---|
3. Joined result
baseline: 4
baseline: 4
baseline: 16
| Source | Lookup | Student ID | Grade | Days |
|---|
A join can change the meaning of a total
Keeping unmatched rows prevents silent loss. It does not fill the missing information. And a left join can still multiply rows when the lookup contains more than one match.
Why does the missing student disappear?
An inner join keeps only attendance rows that find a match in the lookup. In the missing-student example, A4 has no eligible lookup row, so its four days present disappear from the result. A left join retains A4 and leaves its grade unmatched, making the gap visible for review.
Why does the repeated ID create extra attendance?
A2 matches both E2 and E5. Each match produces an output row, so A2’s three days present are copied twice. The result has five rows but still only four distinct students. Both join methods do this until the ambiguous lookup is resolved.
Repeated IDs are not always bad records. History, multiple schools, or separate program records can be valid. First define the intended unit of record and time window. In this exercise, two conflicting grade rows are eligible for the same student, school, and period, where only one is expected.
Why not just delete the repeated row?
There is no evidence here that grade 6 or grade 7 is the correct value. Ask the record owner to resolve the conflicting enrollment entries or refine the eligibility rule using source facts. Keep the original extract, document the decision, rebuild the lookup, and rerun the comparison.
What changes with real district data?
Student ID alone may not be enough. You may need school, enrollment dates, section, or another approved key. Match the key and effective dates to the question. Check uniqueness on both sides, and review unmatched records in both directions. If the lookup covers a larger population than the extract, some lookup-only students may be expected; document the reason.
Take these checks to your next join
- State the expected row. One row per student? Per student and school? Per attendance date?
- Check the keys before joining. Find blank keys, repeated keys, and records outside the eligible time window.
- Compare after joining. Check row count, distinct keys, totals, unmatched rows, and rows that gained extra matches.
- Resolve and rerun. Have the source owner confirm facts. Preserve the originals and record the matching and filtering rules.
Even matching totals do not prove that the same students or values are present. Inspect the record matches as well as the counts.
The comparison includes the selected setup, both input tables, the result, and the next investigation. It contains only the fictional records shown here.