Scenario

Write inner joins on verified keys: complete ON conditions, predict one-to-many output, qualify ambiguous columns, repair wrong ID joins, and preserve line-item grain.

INNER JOIN and Qualified Columns / debug query

One-sentence task

M17-A05 - Debug - repair a plausible but wrong ID-to-ID join. Combine matching rows on verified keys with readable aliases and qualified references.

Structured output contract

Result grain

one correctly matched order row with customer identifier

Exact columns

order_id; customer_id; customer_name

Source population

Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.

Grouping

Do not collapse rows unless the contract explicitly asks for aggregation, distinct tuples, or set semantics.

Ordering

return order_id; repair the join to use orders.customer_id; order by order_id

Validation

select-only; hidden deterministic variants.

Relevant tables

Time and difficulty

Estimated time

9 minutes

Difficulty

4/5

Objective and concepts

Debug the requested SQL output contract for inner join and qualified columns using source grain, columns, ordering, and edge-case evidence.

SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;

SELECT o.order_id, o.customer_id AS order_customer_id, c.customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id ORDER BY o.order_id;

Assumptions, dialect notes, and common traps

Duplicate policy

Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.

Null policy

Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.

Tie-breakers

Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.

Zero-related entities

Do not invent zero rows unless the contract asks for preserved parents, missing entities, or complete periods.

Numeric tolerance

Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.

• ON needs the real foreign key: The join condition pairs similarly named primary keys instead of the child foreign key. Repair: Use the verified relationship from the schema: child foreign key to parent primary key.
• One-to-many rows are expected repeats: Repeated parent labels in a joined result are treated as duplicates to remove. Repair: Name the output grain; parent values repeat when each child row is still represented.
• Ambiguous columns need table aliases: A shared column name is selected without qualification after joining tables. Repair: Qualify source columns with table aliases and add readable output aliases when needed.
• Relationship and row filters are different: A status rule is mixed into the join condition and the path becomes hard to audit. Repair: Put key relationships in ON and row filters in WHERE for inner joins.

Opened hints

No hints opened yet.