title: Basic Data Warehouse - How to join data 2 tables away
body: <p>
  I have a basic data warehouse setup that represents the police force:
  <a href="https://i.stack.imgur.com/bfbZZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bfbZZ.png" alt="Data warehouse for the police department"></a></p>
  
  <p>Every <code>FACT_CRIME</code> has an assigned <code>DIM_OFFICER</code>; every <code>DIM_OFFICER</code> is assigned to a <code>DIM_STATION</code>.</p>
  
  <p>What SQL logic can I use to display all <code>FACT_CRIME</code>s along with the associated <code>DIM_STATION</code>? My issue is that to obtain the <code>DIM_STATION</code>.<code>station_code</code>, I know it must be done via the <code>DIM_OFFICER</code>, since that is the relational link, but I'm not savvy enough with SQL to know how to get data from 2 tables away.</p>
  
  <p>
  This is a query I have so far, it produces the format I'm after, but not the correct results:</p>
  
  <pre><code>select
      fact_crime.*, dim_station.station_code
  from fact_crime
  join dim_station
      on fact_crime.assigned_officer_id
          in (select dim_officer.officer_id from dim_officer where dim_officer.station_id = dim_station.station_id)
  group by dim_station.station_code
  </code></pre>
  
  <p>Sample of produced results: (correct format, but incorrect data)
  <a href="https://i.stack.imgur.com/baS0a.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/baS0a.png" alt="Sample of produced results"></a></p>
  
  <p>
  I'm expecting every instance of <code>FACT_CRIME</code> to appear, with the assigned officer's station code additionally appearing in the table.</p>
  
  <p>
  Thanks in advance to anyone who can point me in the right direction. I'm still trying to get to grips with the basics of SQL, so apologies if I require additional clarification on any responses.</p>