{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"the-fan-trap","title":"Two child tables, one very wrong total","url":"https://addaly.com/learn/data-and-sql/the-fan-trap","position":17,"of":66,"minutes":9,"answer":"Two one-to-many joins from the same parent multiply each other; pre-aggregate each child to one row per parent, then join the summaries.","keyIdea":"Two one-to-many joins from the same parent multiply each other; pre-aggregate each child to one row per parent, then join the summaries."},"body":{"format":"markdown","text":"## The bug that survives code review\n\nYou have an order. It has line items. It also has payments. Both are perfectly ordinary one-to-many relationships. You write what looks like an obvious query:\n\n```sql\nSELECT\n  o.id,\n  sum(i.qty * i.unit_price) AS goods,\n  sum(p.amount)             AS paid\nFROM orders o\nJOIN order_items i ON i.order_id = o.id\nJOIN payments    p ON p.order_id = o.id\nGROUP BY o.id;\n```\n\nOrder 5001 has 3 line items and 2 payments. The join produces 3 × 2 = 6 rows. Each line item appears twice, so `goods` is double the truth. Each payment appears three times, so `paid` is triple the truth. Neither number errors, neither looks absurd, and the ratio between them is even preserved badly enough that a sanity check on \"did they pay the full amount\" may still pass.\n\nThis is the **fan trap**, sometimes called the chasm trap in its sibling form. It is the single most expensive join bug in business reporting, and it is invisible in the query text. Nothing about those three lines looks wrong.\n\n## Why the multiplication happens\n\nGo back to the mechanism. A join emits one row per match. When you join the parent to child table A, you get one row per A. When you then join that result to child table B, *each of those rows* looks for matches in B, so every A row is paired with every B row for the same parent. The output is the Cartesian product of the two children, per parent.\n\n> Figure: Order 5001: three line items, two payments, six joined rows. Bars (rupees reported for one order whose true figures are both 4,500): Goods, in order_items 4500; Goods, as the joined query reports 9000; Paid, in payments 4500; Paid, as the joined query reports 13500. Each line item is emitted once per payment, so goods double. Each payment is emitted once per line item, so paid triples. Neither number errors and neither looks absurd. Pre-aggregate each child to one row per order, then join the summaries.\n\nThe rule: **two independent one-to-many joins from the same parent multiply each other.** Three children multiply three ways. It gets worse quickly: 5 items, 3 payments and 4 shipments give 60 rows for one order.\n\n## Spotting it\n\nTwo signs, both cheap.\n\nFirst, the row count. If the joined result has far more rows than either child table, something is fanning. Second, an \"impossible\" total: revenue larger than the company's revenue, quantities that exceed inventory, a customer who apparently paid eleven times. Distrust any total you did not check against a known figure.\n\nBut the reliable detection is structural, not numerical: **count the one-to-many joins in the FROM clause.** More than one from the same parent, and you have a fan trap unless you did something about it.\n\n## The fixes\n\n**Pre-aggregate each child separately, then join the summaries.** This is the correct default:\n\n```sql\nWITH item_totals AS (\n  SELECT order_id, sum(qty * unit_price) AS goods\n  FROM order_items GROUP BY order_id\n),\npayment_totals AS (\n  SELECT order_id, sum(amount) AS paid\n  FROM payments GROUP BY order_id\n)\nSELECT o.id,\n       coalesce(it.goods, 0) AS goods,\n       coalesce(pt.paid, 0)  AS paid\nFROM orders o\nLEFT JOIN item_totals    it ON it.order_id = o.id\nLEFT JOIN payment_totals pt ON pt.order_id = o.id;\n```\n\nEach CTE has grain \"one row per order\". Joining one-to-one preserves it. Nothing multiplies, and the LEFT JOINs keep orders that have no payment yet, which is usually the interesting group.\n\n**Or use scalar subqueries**, when there are only one or two values and readability matters more than speed:\n\n```sql\nSELECT o.id,\n  (SELECT sum(qty * unit_price) FROM order_items WHERE order_id = o.id) AS goods,\n  (SELECT sum(amount)           FROM payments    WHERE order_id = o.id) AS paid\nFROM orders o;\n```\n\nEach subquery returns exactly one value per order, so the grain cannot change. On modern Postgres the planner often rewrites this into something close to the CTE version; on other engines it can run once per row, which is fine for a thousand orders and painful for a million.\n\n**Or `count(DISTINCT ...)`, but only for counts.** `count(DISTINCT i.id)` survives the fan because it deduplicates. `sum(i.qty)` does not, and there is no `sum(DISTINCT)` that means what you want: `sum(DISTINCT amount)` would collapse two separate payments of ₹500 into one. This is why reaching for DISTINCT to \"fix\" a total is dangerous: it works for counting and corrupts summing.\n\n## The chasm trap, briefly\n\nThe related failure is joining two children that have *no* rows in common, so an inner join returns nothing for orders that have items but no payments yet, and those are precisely the unpaid orders your report was about. Same cause, opposite symptom: the fan inflates, the chasm deletes. LEFT JOINs on pre-aggregated summaries avoid both.\n\n## The habit worth keeping\n\nBefore writing a query with more than one join, list the tables and write \"one\" or \"many\" next to each. If two \"many\" tables hang off the same parent, stop and pre-aggregate. It takes fifteen seconds and it is the difference between a report you can defend and one that is wrong by a factor of six."},"check":{"question":"A query on `orders` joins both `order_items` and `shipments` (about four shipments per order) and sums `qty * unit_price`, giving goods totals roughly four times the true value. Which fix is correct?","options":[{"text":"Add `DISTINCT` to the SELECT list so the duplicated rows are collapsed before aggregation, which restores one row per line item.","correct":false,"tempting":"DISTINCT removes rows that are identical in every column, so two shipments of the same quantity on the same day would be merged while genuinely duplicated rows with differing ids survive."},{"text":"Change both joins to LEFT JOINs so no rows are dropped.","correct":false,"tempting":"Changes what happens to unmatched rows and does nothing about matched ones; the multiplication is unaffected."},{"text":"Group by `order_id, item_id, shipment_id` so each combination is counted once.","correct":false,"tempting":"Counts each combination once, but there are still four rows per shipment in the output and any sum over them is still four times too large."},{"text":"Aggregate `order_items` and `shipments` separately to one row per order first, then join those two summaries to `orders`.","correct":true}]},"prev":{"slug":"many-to-many-bridge-tables","title":"Many-to-many, and the table in the middle","url":"https://addaly.com/learn/data-and-sql/many-to-many-bridge-tables"},"next":{"slug":"self-joins","title":"Joining a table to itself","url":"https://addaly.com/learn/data-and-sql/self-joins"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Two child tables, one very wrong total', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/the-fan-trap (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Two child tables, one very wrong total","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"Two one-to-many joins from the same parent multiply each other; pre-aggregate each child to one row per parent, then join the summaries.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"rupees reported for one order whose true figures are both 4,500","title":"Order 5001: three line items, two payments, six joined rows","series":[{"label":"Goods, in order_items","value":4500},{"label":"Goods, as the joined query reports","value":9000,"highlight":true},{"label":"Paid, in payments","value":4500},{"label":"Paid, as the joined query reports","value":13500,"highlight":true}],"caption":"Each line item is emitted once per payment, so goods double. Each payment is emitted once per line item, so paid triples. Neither number errors and neither looks absurd. Pre-aggregate each child to one row per order, then join the summaries."},"alt":"Figure: Order 5001: three line items, two payments, six joined rows. Bars (rupees reported for one order whose true figures are both 4,500): Goods, in order_items 4500; Goods, as the joined query reports 9000; Paid, in payments 4500; Paid, as the joined query reports 13500. Each line item is emitted once per payment, so goods double. Each payment is emitted once per line item, so paid triples. Neither number errors and neither looks absurd. Pre-aggregate each child to one row per order, then join the summaries."}},"blocks":[{"t":"h2","id":"the-bug-that-survives-code-review","runs":[{"text":"The bug that survives code review"}]},{"t":"p","runs":[{"text":"You have an order. It has line items. It also has payments. Both are perfectly ordinary one-to-many relationships. You write what looks like an obvious query:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  o.id,\n  sum(i.qty * i.unit_price) AS goods,\n  sum(p.amount)             AS paid\nFROM orders o\nJOIN order_items i ON i.order_id = o.id\nJOIN payments    p ON p.order_id = o.id\nGROUP BY o.id;"},{"t":"p","runs":[{"text":"Order 5001 has 3 line items and 2 payments. The join produces 3 × 2 = 6 rows. Each line item appears twice, so "},{"text":"goods","code":true},{"text":" is double the truth. Each payment appears three times, so "},{"text":"paid","code":true},{"text":" is triple the truth. Neither number errors, neither looks absurd, and the ratio between them is even preserved badly enough that a sanity check on “did they pay the full amount” may still pass."}]},{"t":"p","runs":[{"text":"This is the "},{"text":"fan trap","b":true},{"text":", sometimes called the chasm trap in its sibling form. It is the single most expensive join bug in business reporting, and it is invisible in the query text. Nothing about those three lines looks wrong."}]},{"t":"h2","id":"why-the-multiplication-happens","runs":[{"text":"Why the multiplication happens"}]},{"t":"p","runs":[{"text":"Go back to the mechanism. A join emits one row per match. When you join the parent to child table A, you get one row per A. When you then join that result to child table B, "},{"text":"each of those rows","i":true},{"text":" looks for matches in B, so every A row is paired with every B row for the same parent. The output is the Cartesian product of the two children, per parent."}]},{"t":"p","runs":[{"text":"The rule: "},{"text":"two independent one-to-many joins from the same parent multiply each other.","b":true},{"text":" Three children multiply three ways. It gets worse quickly: 5 items, 3 payments and 4 shipments give 60 rows for one order."}]},{"t":"h2","id":"spotting-it","runs":[{"text":"Spotting it"}]},{"t":"p","runs":[{"text":"Two signs, both cheap."}]},{"t":"p","runs":[{"text":"First, the row count. If the joined result has far more rows than either child table, something is fanning. Second, an “impossible” total: revenue larger than the company’s revenue, quantities that exceed inventory, a customer who apparently paid eleven times. Distrust any total you did not check against a known figure."}]},{"t":"p","runs":[{"text":"But the reliable detection is structural, not numerical: "},{"text":"count the one-to-many joins in the FROM clause.","b":true},{"text":" More than one from the same parent, and you have a fan trap unless you did something about it."}]},{"t":"h2","id":"the-fixes","runs":[{"text":"The fixes"}]},{"t":"p","runs":[{"text":"Pre-aggregate each child separately, then join the summaries.","b":true},{"text":" This is the correct default:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH item_totals AS (\n  SELECT order_id, sum(qty * unit_price) AS goods\n  FROM order_items GROUP BY order_id\n),\npayment_totals AS (\n  SELECT order_id, sum(amount) AS paid\n  FROM payments GROUP BY order_id\n)\nSELECT o.id,\n       coalesce(it.goods, 0) AS goods,\n       coalesce(pt.paid, 0)  AS paid\nFROM orders o\nLEFT JOIN item_totals    it ON it.order_id = o.id\nLEFT JOIN payment_totals pt ON pt.order_id = o.id;"},{"t":"p","runs":[{"text":"Each CTE has grain “one row per order”. Joining one-to-one preserves it. Nothing multiplies, and the LEFT JOINs keep orders that have no payment yet, which is usually the interesting group."}]},{"t":"p","runs":[{"text":"Or use scalar subqueries","b":true},{"text":", when there are only one or two values and readability matters more than speed:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.id,\n  (SELECT sum(qty * unit_price) FROM order_items WHERE order_id = o.id) AS goods,\n  (SELECT sum(amount)           FROM payments    WHERE order_id = o.id) AS paid\nFROM orders o;"},{"t":"p","runs":[{"text":"Each subquery returns exactly one value per order, so the grain cannot change. On modern Postgres the planner often rewrites this into something close to the CTE version; on other engines it can run once per row, which is fine for a thousand orders and painful for a million."}]},{"t":"p","runs":[{"text":"Or ","b":true},{"text":"count(DISTINCT ...)","b":true,"code":true},{"text":", but only for counts.","b":true},{"text":" "},{"text":"count(DISTINCT i.id)","code":true},{"text":" survives the fan because it deduplicates. "},{"text":"sum(i.qty)","code":true},{"text":" does not, and there is no "},{"text":"sum(DISTINCT)","code":true},{"text":" that means what you want: "},{"text":"sum(DISTINCT amount)","code":true},{"text":" would collapse two separate payments of ₹500 into one. This is why reaching for DISTINCT to “fix” a total is dangerous: it works for counting and corrupts summing."}]},{"t":"h2","id":"the-chasm-trap-briefly","runs":[{"text":"The chasm trap, briefly"}]},{"t":"p","runs":[{"text":"The related failure is joining two children that have "},{"text":"no","i":true},{"text":" rows in common, so an inner join returns nothing for orders that have items but no payments yet, and those are precisely the unpaid orders your report was about. Same cause, opposite symptom: the fan inflates, the chasm deletes. LEFT JOINs on pre-aggregated summaries avoid both."}]},{"t":"h2","id":"the-habit-worth-keeping","runs":[{"text":"The habit worth keeping"}]},{"t":"p","runs":[{"text":"Before writing a query with more than one join, list the tables and write “one” or “many” next to each. If two “many” tables hang off the same parent, stop and pre-aggregate. It takes fifteen seconds and it is the difference between a report you can defend and one that is wrong by a factor of six."}]}],"check":{"question":[{"text":"A query on "},{"text":"orders","code":true},{"text":" joins both "},{"text":"order_items","code":true},{"text":" and "},{"text":"shipments","code":true},{"text":" (about four shipments per order) and sums "},{"text":"qty * unit_price","code":true},{"text":", giving goods totals roughly four times the true value. Which fix is correct?"}],"options":[{"text":[{"text":"Add "},{"text":"DISTINCT","code":true},{"text":" to the SELECT list so the duplicated rows are collapsed before aggregation, which restores one row per line item."}],"correct":false,"tempting":[{"text":"DISTINCT removes rows that are identical in every column, so two shipments of the same quantity on the same day would be merged while genuinely duplicated rows with differing ids survive."}]},{"text":[{"text":"Change both joins to LEFT JOINs so no rows are dropped."}],"correct":false,"tempting":[{"text":"Changes what happens to unmatched rows and does nothing about matched ones; the multiplication is unaffected."}]},{"text":[{"text":"Group by "},{"text":"order_id, item_id, shipment_id","code":true},{"text":" so each combination is counted once."}],"correct":false,"tempting":[{"text":"Counts each combination once, but there are still four rows per shipment in the output and any sum over them is still four times too large."}]},{"text":[{"text":"Aggregate "},{"text":"order_items","code":true},{"text":" and "},{"text":"shipments","code":true},{"text":" separately to one row per order first, then join those two summaries to "},{"text":"orders","code":true},{"text":"."}],"correct":true,"tempting":null}],"order":[0,2,3,1],"why":null},"prev":{"slug":"many-to-many-bridge-tables","title":"Many-to-many, and the table in the middle","minutes":8},"next":{"slug":"self-joins","title":"Joining a table to itself","minutes":8}}}