AI table joins add rows: verify five rows, three people and duplicate keys

Lay out the inputs and the actual questions

All people and courses in this original exercise are fictional. The student table contains S1/Ada, S2/Bo and S3/Cy, with unique StudentID values. Four enrollment rows are E1/S1/Math, E2/S1/Art, E3/S2/Math and E4/S2/Math. Ask for total students and students with enrollment records separately, rather than requesting an ambiguous total.

E3 and E4 share a course name but have different enrollment IDs. They could be duplicate entry or legitimately separate business records; this exercise treats them as four supplied details and leaves that question open. For a related identity problem, review the stable-ID and same-name merge checks.

Expand the left join one student at a time

PostgreSQL’s join documentation explains matched-row expansion and the unmatched row retained by a left join. KitNelo’s data cleaning and analysis tool provides a browser-based join-by-key workflow. The exercise uses that feature; it does not require a database or claim that all database features are available here.

Calculate the expected expansion first: S1 matches E1 and E2, S2 matches E3 and E4, and S3 has no match but remains in a left join. Two plus two plus one gives five. The tool keeps source-side fields separate and writes blank right-side fields for an unmatched row. That output representation differs from database nulls and cannot by itself explain every possible missing value.

Give each measure its own unit and evidence

Total headcount comes from the three distinct StudentID values in the student table. Enrolled headcount comes from distinct students with actual EnrollmentID matches: S1 and S2. Matched enrollment records are E1 through E4, giving four. Only the final joined-row count is five. Attach a noun to every number instead of labeling each one total.

Do not turn S3’s missing match into a course with ID zero or conclude that the retained row represents an enrollment. If asked for enrollment-record counts per student, the answers are S1:2, S2:2 and S3:0. That zero is a count within the supplied table, not an invented course-field value. See the missing-versus-zero checks for that distinction.

Ask AI for the reconstruction and unresolved cases

The proposed prompt supplies all fictional input, making its reasoning inspectable. It is not an accuracy benchmark for a model. Do not paste real student identities into an unauthorized service; establish the method with the small exercise before using data you have permission to process.

Inspect more than the final numbers 3, 2, 4 and 5. Match every output row to an input ID, confirm that S3 remains, and check that E3 and E4 were not silently collapsed. No enrollment should appear without an input record. Retaining IDs makes verification independent of the assistant’s explanation.

Use only this fictional data.
Students: S1 Ada; S2 Bo; S3 Cy.
Enrollments: E1 S1 Math; E2 S1 Art; E3 S2 Math; E4 S2 Math.
Left join on StudentID and list every output row. Mark unmatched right-side fields explicitly.
Separately count total students, distinct students with enrollments, nonempty enrollment records and output rows. Explain the differences.
Do not deduplicate by course name or invent IDs. List business ambiguities needing human review.

Check whether the left table is itself duplicated

If S1 accidentally appears twice in the student table while two enrollment rows still match S1, that student produces 2 × 2 = 4 pairings. Check the left-side uniqueness rule instead of dismissing every increase as normal join behavior. If left rows intentionally represent different terms, the business key may need a term field as well.

Do not apply final-output deduplication simply to make headcount look reasonable. That can remove valid details while concealing the input problem. Document input uniqueness, match multiplicity and reporting units separately; keep the result under review while any of those remain unclear.

Run a left join from the two downloadable source files

Unpack students.csv, enrolments.csv, students-duplicate.csv and expected.json from the exercise ZIP on this page. The first three are inputs; expected.json is an editorial reference answer. For the normal run, import only students.csv and enrolments.csv. Neither the fault copy nor the answer file is another business table. All people and records are fictional.

After import, current working data initially shows the first table only. The second file has not disappeared. Join operations read the two selected original tables. Do not vertically append them, or assume that cleaning the current working table has replaced the original join inputs.

1. 1
   Open Data cleaning and analysis and select students.csv plus enrolments.csv together. Check original imports of three and four rows.
2. 2
   Expand “Join and compare (original tables)”. Select students on the left, enrolments on the right and StudentID as Comparison key.
3. 3
   Choose “Left join: keep all left rows”, then “Join by key”.
4. 4
   Expect five preview rows. Verify S1 with E1/E2, S2 with E3/E4 and S3 with blank right-side enrolment and course fields.
5. 5
   Download the current CSV and reopen it separately. Check the five rows by source-side fields and IDs. Seven combined input records are not seven people.

To summarize the result, first choose “Continue with this result”. Rerunning a join still reads the selected original tables. Correct and reimport source files when those join inputs must change.

Recover the seven-row fault at its source

In a fresh tool page, import students-duplicate.csv and the same enrolments.csv. This master has four rows because S1/Ada is repeated. Under the exercise’s one-person-per-master-row rule, that repetition is a deliberate fault. The left join should produce seven rows: four for S1, two for S2 and one for S3. E1 and E2 each appear twice because both repeated master rows match them.

Deduplicating the seven-row result by StudentID would discard genuine course detail too. Inspect the faulty master, restore students.csv under this exercise’s explicit identity rule, then reimport it with the original enrolments and rerun. Expect five rows again. If a real master records one row per student and term, define the compound key first; deleting all repeated student IDs would be wrong.

Hand over both source CSVs, the actual joined CSV and a note naming each unit: three students, two students with enrolments, four matched enrolment records and five result rows. Whether E3/E4 are business duplicates remains a separate decision. These reference answers cover a small fictional fixture, not model accuracy or real-user processing success.