title: grouping relationships of many-to-many-to many
body: <p>I have a requirement to map a tax rate to a person based on the country the person was resident at that time.</p>
  
  <pre><code>tbl: person
  | p_id | name_first | name_last |
  =======++========================
  | 1    | john       | smith     |
  | 2    | joanne     | smyth     |
  
  
              tbl: person_in_country
              | p_id | iso | arrived    |
              ===========================
              | 1    | GB  | 1980-01-01 |
              | 2    | FR  | 1987-03-21 |
              | 1    | FR  | 2003-06-17 |
              | 1    | JP  | 2008-07-02 |
              | 2    | GB  | 2008-10-01 |
              | 1    | GB  | 2009-01-10 |
  
  tbl: country
  | iso | ctry_name      |
  ========================
  | GB  | United Kingdom |
  | FR  | France         |
  | JP  | Japan          |
  
              tbl: tax_rates
              | iso | tax_rate | tax_date   |
              ===============================
              | GB  | 17.5     | 1970-01-01 |
              | FR  | 15.0     | 1977-03-21 |
              | JP  | 12.0     | 1977-06-17 |
              | FR  | 15.0     | 1994-03-21 |
              | JP  | 18.5     | 2008-07-02 |
              | GB  | 15       | 2008-04-01 |
              | GB  | 20       | 2010-05-01 |
  </code></pre>
  
  <p>So I need tuples containing the person in the country and the tax rate that they should have at a given time..</p>
  
  <p>Something along the lines of:</p>
  
  <pre><code>select p.p_id, p.name_first, p.name_last,
         pic.arrived,
         c.iso, c.ctry_name,
         t.tax_rate
  from people p
  left join (select * from person_in_country order by arrived desc) pic using (p_id)
  left join country c on c.iso = pic.iso
  left join (select * from tax_rates order by tax_date desc) t on t.iso = c.iso
  where t.tax_date &lt;= NOW()
  group by p.pid, pic.arrived, t.tax_date
  </code></pre>
  
  <p>Hope this make sense... and many thanks in advance</p>