Variable number of joins
March 25, 2009
Iggy Fernandezs posted a challenge which looks like routine application of recursive query technique:
with njoins (face_value, probability, joins) as ( SELECT d1.face_value , d1.probability, 1 FROM die d1 union all SELECT d1.face_value + d2.face_value AS SUM, d1.probability * d2.probability AS probability, joins+1 FROM njoins d1, die d2 where d1.joins < 10 ) select * from njoins
This is expected to work on Oracle 11.2, DB2, SQL Server (the later two not tested). Filtering out the result with joins = :n aggregating and grouping is left as an exercise to the reader.