title: Shared Hierarchy duplication and LEFT JOIN
body: <p>Let's assume that I can only do this in SQLite.</p>
  
  <p>I have two Tables.</p>
  
  <pre><code>State {
    DistrictID: int
    SubdistrictID: int
    CityID: Int
    name: varchar(36)
  }
  </code></pre>
  
  <p>Theres an obvious heirarchy in the State table. A State has a number of Districts, Each district has a number of Subdistricts, each Subdistrict has a number of Cities.</p>
  
  <p>The other table is the number of Students in a City, where the Students.CityID = State.CityID</p>
  
  <pre><code>Students {
    CityID: int
    number: int
  }
  </code></pre>
  
  <p>I want to know take out the number of Students existing in a District '221'.</p>
  
  <p><strong>QUERY</strong>: <code>SELECT sum(students.number) from State LEFT JOIN Students students ON State.CityID = students.CityID WHERE State.DistrictID = 221 GROUP BY State.DistrictID</code></p>
  
  <p>So far so good, this does work. I get the total number of students from across all the cities that are in District 221.  </p>
  
  <h3>Heres the complication.</h3>
  
  <p>Some <strong>Cities are SHARED between Subdistricts</strong>. In this case, a District have One city thats shared across two Subdistricts. This is reflected within the State Table.</p>
  
  <pre><code>State Table
  Row0: DistrictID: 221; SubDistrictID: 332; CityID: 554
  Row1: DistrictID: 221; SubDistrictID: 332; CityID: 555
  Row2: DistrictID: 221; SubDistrictID: 333; CityID: 554
  Row3: DistrictID: 221; SubDistrictID: 333; CityID: 557
  </code></pre>
  
  <p>The Rows 0 and 2 have the same city (554) shared across two Subdistricts - 332 and 333. </p>
  
  <p>In this case, the above sql query would double the SUM() value as the same city is counted TWICE.</p>
  
  <p>How am I to solve this complex duplication that logically arose because of this technically faulty, but realistical problem, by not changing the schema of the table? I tried using <code>distinct</code>, but it doesn't fit this purpose, and as such, doesn't work.</p>