multiple joins to the same table

[jbothma]

if you have two joins to the same table, it uses the same reference for that table so the join will only return rows where both fact table columns are equal and match some row in the dimension table.

It'd be nice to support cases where you need to join to the same table twice, but where the join columns don't all equal.

To support that we need to alias the dimension table differently for each join - perhaps according to the column being joined to.

sorry - don't have time to make an example model but this might suffice:

demarcation_changes

• demensions
  • old_demarcation
    • attributes
      • demarcation.code
      • demarcation.label
    • join_column: code
  • new_demarcation
    • attributes
      • demarcation.code
      • demarcation.label
    • join_column: code

This generates a query including

FROM demarcation_changes, demarcation
WHERE demarcation_changes.old_code = demarcation.code AND demarcation_changes.new_code = demarcation.code

while we want

FROM demarcation_changes, demarcation as old_demarcation, demarcation as new_demarcation
WHERE demarcation_changes.old_code = old_demarcation.code 
AND demarcation_changes.new_code = new_demarcation.code