title: SQL select addresses from a state in single query from 2 tables
body: <p>I don't know if the title makes any sense, but here is the situation: See 2 tables below</p>
  
  <p><strong>People:</strong> (BTW, if it helps to know, this table will never have >1000 rows)</p>
  
  <pre><code>+----+---------+-------------------+---------+
  | id |  name   |      address      | city_id |
  +----+---------+-------------------+---------+
  |  1 | person1 | some address      |     123 |
  |  2 | person2 | another address   |     542 |
  |  3 | person3 | different address |     623 |
  +----+---------+-------------------+---------+
  </code></pre>
  
  <p><strong>Cities:</strong> (this one may contain all cities with states(and addl. column for country) around the globe)</p>
  
  <pre><code>+-----+-------+--------+
  | id  | city  | state  |
  +-----+-------+--------+
  | 123 | city1 | state1 |
  | 542 | city2 | state1 |
  | 623 | city3 | state2 |
  +-----+-------+--------+
  </code></pre>
  
  <p>To start, I know only <code>people.id</code>. Using this I need to find all people that belong to same <code>state</code> (<strong>not same <code>city</code></strong>). For example, if I have <code>people.id=1</code>, I need to get all people from the <strong>state</strong> that person1 (people.id = 1) belongs to:</p>
  
  <p><strong>Output:</strong></p>
  
  <pre><code>+----+---------+-----------------+---------+
  | id |  name   |     address     | city_id |
  +----+---------+-----------------+---------+
  |  1 | person1 | some address    |     123 |     /*Both the people are from state1*/
  |  2 | person2 | another address |     542 |
  +----+---------+-----------------+---------+
  </code></pre>
  
  <p>I'm able to achieve this in two queries: A variable <code>$state</code> storing output of </p>
  
  <p><code>SELECT c.state from people p INNER JOIN cities c ON p.city_id=c.id where p.id=&lt;my input&gt;;</code></p>
  
  <p>and then another query</p>
  
  <p><code>SELECT a.* FROM</code>people<code>a INNER JOIN</code>cities<code>b ON a.city_id=b.id WHERE b.state=$state</code></p>
  
  <p>Is there a more efficient way to achieve this with a single JOIN? I tried combining the two queries to <code>SELECT</code> with JOIN within a JOIN(in subquery) which doesn't feel right somehow. </p>
  
  <p><strong>P.S: <em>I'm not looking for recommendations on normalization or other changes to schema. All that is already in consideration for another development branch for later upgrade.</em></strong></p>