| Course | HI520 Database Design and SQL |
|---|---|
| Unit | Unit 6 |
| Paper type | Multi-table join query |
| Length | About 840 words, 6 pages double-spaced |
| Format | APA 7 student paper |
| School | Purdue University Global |
| Program | MS in Health Informatics |
| Updated | October 2026 |
Free sample paper for HI520 Unit 6
412 Children With Persistent Asthma, 238 After an Inner Join: A Unit 6 Multi-Table Join Query Written Three Ways and Reconciled Patient by Patient
[Student Name]
Purdue University Global
HI520: Database Design and SQL
Unit 6 Multi-Table Join Query
[Instructor Name]
[Date]
Calder Bay Pediatrics and every row and count are synthetic; the sources cited are real.
The Question and Its Population
The quality team asked how many children with persistent asthma had spirometry in the past year, since national guidelines recommend lung function testing for children five and older at diagnosis and periodically afterward (National Asthma Education and Prevention Program [NAEPP], 2007). The population is every patient with at least one encounter diagnosis in the persistent asthma code range at an encounter starting on or after July 1, 2025, and before July 1, 2026. The outcome is a spirometry result in the same window.
Base Count
SELECT COUNT(DISTINCT e.patient_id) FROM encounter e JOIN encounter_diagnosis ed ON ed.encounter_id = e.encounter_id WHERE (ed.code LIKE 'J45.3%' OR ed.code LIKE 'J45.4%' OR ed.code LIKE 'J45.5%') AND e.start_time >= DATE '2025-07-01' AND e.start_time < DATE '2026-07-01'; Result: 412 distinct patients. Every later version must account for these 412 children, no more and no fewer.
Version 1: Inner Joins All the Way
SELECT DISTINCT e.patient_id FROM encounter e JOIN encounter_diagnosis ed ON ed.encounter_id = e.encounter_id JOIN test_order o ON o.encounter_id = e.encounter_id JOIN test_result r ON r.order_id = o.order_id WHERE (ed.code LIKE 'J45.3%' OR ed.code LIKE 'J45.4%' OR ed.code LIKE 'J45.5%') AND o.test_code = 'SPIRO' AND e.start_time >= DATE '2025-07-01' AND e.start_time < DATE '2026-07-01'; Result: 238 distinct patients. The inner join to test_result keeps only children whose spirometry order has a result, so the 165 children with no spirometry disappear without any warning. The query also requires the spirometry to be ordered at the same encounter that carried the diagnosis, which drops another 9 children whose spirometry was ordered at a different visit. A reader who saw only this query would report 238 tested and would not know how many were untested. An inner join answers only the children who have every piece of data it asks for, and it never says how many it left behind.
Version 2: A Left Join That Keeps Everyone and Repeats Some
The second version starts from the base population and joins spirometry results with a LEFT JOIN, matching on the patient rather than the encounter, so any spirometry in the window counts. SELECT p.patient_id, r.result_id, r.result_time FROM (SELECT DISTINCT e.patient_id FROM encounter e JOIN encounter_diagnosis ed ON ed.encounter_id = e.encounter_id WHERE (ed.code LIKE 'J45.3%' OR ed.code LIKE 'J45.4%' OR ed.code LIKE 'J45.5%') AND e.start_time >= DATE '2025-07-01' AND e.start_time < DATE '2026-07-01') p LEFT JOIN encounter e2 ON e2.patient_id = p.patient_id LEFT JOIN test_order o ON o.encounter_id = e2.encounter_id AND o.test_code = 'SPIRO' LEFT JOIN test_result r ON r.order_id = o.order_id AND r.result_time >= DATE '2025-07-01' AND r.result_time < DATE '2026-07-01'; Result: 1,106 rows covering all 412 patients. Every child is present, but children appear many times: once for every encounter that had no spirometry order, and once for each result, including the pre-bronchodilator and post-bronchodilator values of a single test. Counting rows would report 1,106 children. This repetition, often called fan-out, is a finding in itself: any total built on these rows without COUNT(DISTINCT patient_id) would be wrong by a factor of almost three.
Version 3: One Row Per Child, Tested or Not
SELECT p.patient_id, EXISTS (SELECT 1 FROM encounter e2 JOIN test_order o ON o.encounter_id = e2.encounter_id JOIN test_result r ON r.order_id = o.order_id WHERE e2.patient_id = p.patient_id AND o.test_code = 'SPIRO' AND r.result_time >= DATE '2025-07-01' AND r.result_time < DATE '2026-07-01') AS had_spirometry FROM (the base population subquery from version 2) p; Result: 412 rows, one per child, with had_spirometry true for 247 and false for 165. The EXISTS test asks a yes-or-no question for each child and cannot repeat anyone, so the output can be counted directly.
Reconciliation
1. Base population: 412 children.
2. Version 1, inner joins, same encounter only: 238 children, missing 174.
3. Of the 174 missing from version 1: 165 had no spirometry result anywhere in the window, and 9 had spirometry ordered at a different encounter from the one carrying the diagnosis.
4. Version 2, left joins: 412 children across 1,106 rows.
5. Version 3, EXISTS: 412 children, 247 tested and 165 not tested.
6. Check: 247 plus 165 equals 412, and 238 plus 9 equals 247.
What the Team Should Report
The correct answer to the quality team's question is 247 of 412 children, or 60 percent, had spirometry in the past year. Version 1 would have reported 238 and hidden the denominator, and version 2 would have produced an inflated count. The 165 untested children are the actionable list. The relational model makes joins easy to write (Codd, 1970), which is exactly why each one needs a stated population and a reconciliation before its result is trusted, a habit data quality frameworks describe as checking completeness against an expected count (Weiskopf & Weng, 2013).
References
Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685
National Asthma Education and Prevention Program. (2007). Expert panel report 3: Guidelines for the diagnosis and management of asthma. National Heart, Lung, and Blood Institute.
Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681
HI520 Unit 6 instructions, in plain terms
Unit 6 typically asks you to write queries that join several tables to answer a clinical question, and to explain how different join types change the result. Expect to state the population first, take a base count from a single table, and then compare versions such as inner joins, left joins and subqueries. Many instructors want rows returned and distinct patients reported for each version, so fan-out is visible. A reconciliation showing where every patient went is the strongest evidence of understanding. You should explain which version answers the question correctly and why. Cite the clinical guidance behind the question and a database or data quality source for the method. Use synthetic or composite data, present SQL in full, and avoid relying on a single join without checking what it removed.
How the HI 520 Unit 6 example is put together
The paper states the quality team's question and the guideline behind it, then defines the population and the window. A base count with COUNT DISTINCT gives 412 children. Version 1 chains inner joins and returns 238, and the highlighted sentence warns that an inner join never reports what it left out. Version 2 uses left joins from the base population and returns 1,106 rows for 412 children, with fan-out explained by visits without orders and paired spirometry results. Version 3 uses EXISTS to return one row per child with a true or false flag. A reconciliation list accounts for every child across versions and checks that the numbers add up. The closing section gives the correct answer and the actionable list, and grounds the method in Codd's model and completeness checking.
Reading the HI520 Unit 6 grading rubric
Join assignments are generally graded on correct SQL, population definition, reconciliation and interpretation. Correct SQL earns the base share, but most marks depend on whether the student understands what each join does to the population. Graders reward a base count taken before joining and a clear statement of rows and distinct patients for each version. Reconciliation often carries the largest share, since it shows that every patient is accounted for. Students lose many points by reporting an inner join's count as the answer without noticing who was dropped, or by counting fanned-out rows. Interpretation earns credit for choosing the right version and explaining why. Readable SQL, a stated window and population, and APA citations complete the rubric.
HI520 Unit 6 help from the desk
The usual problem in Unit 6 is trusting the first join that runs, typically a chain of inner joins that silently removes patients without data in every table. Another is switching to a left join and then counting rows, which inflates the total through fan-out. Some students join on the wrong level, such as encounter instead of patient, and miss tests done at other visits. Base counts are often skipped, so there is nothing to reconcile against. Reconciliation tables that do not add up undermine the whole analysis. Explanations that describe join syntax rather than effects on the population score lower. Should your assignment use a different question, send the schema, the question and the time window, and the versions can be compared against a base count.
Get an HI520 Unit 6 example written to your instructions
Join prompts in HI520 name the tables and the question, and sometimes supply a database to run against. Send those, the Unit 6 rubric and the table definitions if no file is provided. Queries come back written against that schema, with a count for every version and the dropped rows named, within 24-48h. The first custom sample is free. The paper above is an original model document written by our desk, not a submitted student paper and not an official Purdue University Global document.
More HI520 papers and MS in Health Informatics samples
- HI520 Unit 1: Discussion Post
- HI520 Unit 2: Entity Relationship Diagram
- HI520 Unit 3: Normalization Exercise
- HI520 Unit 4: Schema Build
- HI520 Unit 5: Basic Query Set
- HI520 Unit 7: Aggregation and Grouping Query
- HI520 Unit 8: Seminar Reflection
- HI520 Unit 9: Data Governance Memo
- HI520 Unit 10: Database Design Report
- HI510 Unit 6: Registry Submission Analysis
- HI501 Unit 6: Adoption Evaluation
HI520 Unit 6 questions, answered
Where can I find a free HI520 Unit 6 sample paper?
This page holds the whole Unit 6 join analysis, reconciling 412 children across three versions of one question.
Why did the inner join return fewer children?
It kept only children with a spirometry result linked to the same encounter, dropping 165 never tested and 9 tested at another visit.
What is fan-out in a join?
Repetition of a patient across many rows when joined to tables with several matches, which inflates any row count.
How does EXISTS avoid fan-out?
It asks a yes-or-no question for each child, so each appears once regardless of how many matching results exist.
What was the correct answer in the sample?
247 of 412 children, 60 percent, had spirometry in the past year, with 165 on the untested list.