title: Writing a MySQL statement with multiple joins
body: <p>I have 4 tables, they are: </p>
  
  <pre><code>job, community, state, and region.  
  </code></pre>
  
  <p>Their structure is as follows:</p>
  
  <pre><code>job:
  columns:
    id
    community_id
  relations:
    community:  local_key: community_id, foreign_key: id
  
  community:
  columns:
    id
    state_id
  relations: 
    state: local_key: state_id, foreign_key: id
  
  state:
  columns:
    id
    name
    region_id
  relations:
    region: local_key: region_id, foreign_key: id
  
  region:
  columns:
    id
    name
  </code></pre>
  
  <p>Now, I need a query that will:</p>
  
  <pre><code>get all the jobs with matching communities: i.e.: j.community_id = c.id
  
  then, from those matches, get all of the jobs in communities with a state region_id = "1"
  </code></pre>
  
  <p>I do fine, pulling the state, but I get stuck trying to pull in the region.  I got this far:</p>
  
  <pre><code>SELECT j.id
  FROM job j
  INNER JOIN community c
  ON j.community_id = c.id
  WHERE c.state_id = 35
  </code></pre>
  
  <p>I'm not even sure if my tables are set up correcty in order to retrieve this info.  Any help getting me over the hump would be appreciated.  </p>