title: sqlite foreach in select statement?
body: <p>I have got a problem with an SQLite select.</p>
  
  <p>There are the tables "kategorie","lv","majetok","parcela".</p>
  
  <p>Each person has information about what he owns in the table "majetok". The sample record:</p>
  
  <pre><code>id      idpodfk idlvfk  podiel  podield cislozlv datum1         datum2
  1       31      1       1/10000 0,0001  789      16. 9. 2012    16. 9. 2012
  </code></pre>
  
  <p>idpodfk-fk of person id<br>
  idlvfk-fk of land record id</p>
  
  <p>There is also table LV (table of land records), sample record:</p>
  
  <pre><code>idlv    lvnazov katuzemie
  1       1830    Plavecký Mikuláš
  </code></pre>
  
  <p>Also I use the table "parcela", I store in there information about parcels in the land record</p>
  
  <pre><code>pnazov  rozloha idlvfk  idkategoriafk typ
  5432    692312  1       1             C
  </code></pre>
  
  <p>And table kategorie</p>
  
  <pre><code>idkategoria  meno 
  1            Lesné pozemky
  </code></pre>
  
  <hr>
  
  <p>What I need:
  a foreach record in this select</p>
  
  <pre><code>SELECT   SUM(podield) AS sum1, idlvfk
  FROM     majetok a
  WHERE    idpodfk = 1
  GROUP BY idlvfk
  </code></pre>
  
  <p>I need to select this</p>
  
  <pre><code>SELECT     SUM(par.rozloha)*sum1 AS m2, kat.meno --sum1 from statement above
  FROM       Parcela par 
  INNER JOIN kategorie kat ON par.IDkategoriaFK = kat.IDkategoria
  WHERE      par.IDLVFK = 1 --IDLVFK from statement above
  GROUP BY   kat.meno
  </code></pre>
  
  <p>The (second's) query output should look like this</p>
  
  <pre><code>m2     kat.meno
  123.23 Lesne pozemky    --FOR FIRST IDLVFK
  324.52 Ostatne pozemky  --FOR FIRST IDLVFK   
  235    Pasienky         --FOR FIRST IDLVFK 
  144.23 Lesne pozemky    --FOR NEXT IDLVFK
  543.52 Ostatne pozemky  --FOR NEXT IDLVFK
  756    Pasienky         --FOR NEXT IDLVFK
  .
  .
  .
  </code></pre>
  
  <p>And then I need to group by kat.meno so the final output should look like this:</p>
  
  <pre><code>m2     kat.meno
  267,46 Lesne pozemky
  868,04 Ostatne pozemky
  991    Pasienky
  .                         --other kat.meno if there is
  .                         --other kat.meno if there is
  </code></pre>
  
  <p>Is this possible in one query or do I need to process it on a front-end?</p>
  
  <p>Tables and query background:</p>
  
  <p>There are some parcels in land record (every parcel has its own category, eg forest parcel etc). Person owns a part of a land record (saved in table majetok, eg: 1/1000). That mean he owns 1/1000 from each parcel in that land record. I want to select the area (column "rozloha" in parcela table) that he owns for each category (SUM(par.rozloha)*sum1 AS m2). So we must count sum of all parcels in that category on that land record and multiply with part that owns that person. But he can own 1/1000 on one land record and 3/3456 on the second land record (another land record=another parcels) so we have to do for each land record and that summarize you can see in last code.</p>