Get Reverse Snowflake Joins at SourceForge.net. Fast, secure and Free Open Source software downloads   
  

Fan and Chasm Traps

Fan traps and chasm traps are a consequence of using in same query combinations of tables with mismatching cardinalities. Since Reverse Snowflake Joins is not aware of foreign keys, it can only warn that something is wrong when aggregations are not from the same table.

Chasm traps occur when one table is connected to two or more tables by 1-to-N relationships. This causes multiplication of rows. For example one bad way is to directly try to compare actual sales against sales targets by products.

SELECT
 p.product, SUM(o.sales), SUM(sp.sales)
FROM
 products p INNER JOIN orders o ON p.pid = o.pid
 INER JOIN salesplans sp on p.pid = sp.pid group by p.pid

Fan traps are caused when joining one table by 1-to-N relationship to a second table that is joined to a third table by another 1-to-N relationship. This again is causing multiplication of rows. For example taking the sum of amounts from the orders table and the count of shipped items from the lineitem table.

SELECT
 c.customer, SUM(o.ordertotal), SUM(l.quantity)
  FROM customer c INNER JOIN orders o ON c.cid = o.cid
  INNER JOIN orderlines l ON o.oid = l.oid
  GROUP BY c.customer;