title: Sqlite query Smarter Rows with mathematics of cloned tables?
body: <p>Okay this is pretty complicated to even explain but I will try.<br></p>
  
  <p>Buying table</p>
  
  <pre><code>----------------------------------------------------------------------------------------------------------
  id  itemId  amount  price   bought  collected slot  aborted playerHash
  1   2607    4111    200600  0       0         0     0       1020628
  2   11335   1       0       0       0         3     1       1020628
  3   2495    6546    5306    0       0         1     0       1020628
  4   1127    101     58300   101     0         5     0       37763265
  5   14479   1       107500  0       0         2     0       37763265
  6   1       100     1       0       0         0     0       3        *simulate a problem Buy
  </code></pre>
  
  <p>Selling table</p>
  
  <pre><code>----------------------------------------------------------------------------------------------------------
  id  itemId  amount  price   sold  collected slot  aborted  playerHash
  1   8       8234    132950  7244  0         4     0        1020628
  2   9       1980    132950  0     0         5     0        1020628
  3   9       100     126300  0     0         2     0        1020628
  4   3024    8888    10900   8888  0         0     0        37763265
  5   1       100     1       1     0         0     0        1       *simulate a problem Sell
  6   1       100     1       1     0         0     0        2       *simulate a problem Sell
  </code></pre>
  
  <p>Result of match ups</p>
  
  <pre><code>----------------------------------------------------------------------------------------------------------
  S.itemId  S.amount  S.price  S.sold  S.collected  S.slot  S.playerHash  B.itemId  B.amount  B.price  B.bought  B.collected  B.slot  B.playerHash
  123       2         444      1       0            0       15431         123       34535     448      3         0            1       3455
  123       2         444      1       0            0       15431         123       7567      444      333       0            3       7651
  
  *simulated result rows of wrong data
  
  1         100       1        1       0            0       1             1         100       1        0         0            0       3
  1         100       1        1       0            0       2             1         100       1        0         0            0       3
  </code></pre>
  
  <p>The query I'm trying to do has to match up Buyers of same itemId with Sellers of same itemId.<br>
  Has to make sure both Buyer and Seller didn't have aborted boolean set on their item.
  Also process Buyers who paying more then what the Seller wants first.<br>
  Also check to make sure the amount Seller is selling hasn't been sold yet.<br>
  Also check to make sure the amount Buyer is buying hasn't been bought yet.<br>
  As well as process 100 sales per batch.<br></p>
  
  <p>Query below works great for the most part.<br></p>
  
  <p>Problem I'm trying to fix in the query is to make it more intelligent with mathematics.<br>
  Lets say Selling table had playerHash 1 and playerHash 2<br>
  itemId 8 with amount 100 and sold 1 listed by playerHash lets say 1.<br>
  itemId 8 with amount 100 and sold 1 listed by playerHash lets say 2.<br>
  Now there is 1 Buyer called playerHash 3 who is buying same itemId 8 and amount 100.<br></p>
  
  <p>Problem now is <br>
  playerHash1 can't sell all his 100 amount of itemId 8 because he only has 99 left (sold 1)<br>
  playerHash2 can't sell all his 100 amount of itemId 8 because he only has 99 left (sold 1) same story<br></p>
  
  <p>Now the query should return<br>
  playerHash 1 selling to playerHash3 buyer with a new column to the end of row called something like willBuyAmount set it to 99<br>
  Next row should know that playerHash 1 has sold = 100 (without updating the database and without changing sold column in previous row)<br>
  Dsplay that playerHash 2 selling to playerHash3 buyer with a new coulmn to the end of row called something like willBuyAmount set it to 1.<br>
  playerHash2 should still be able to know by now that it has 98 left (sold 1) + 1 temporary somewhere idk clone the tables maybe?<br>
  and for future buyers of same itemId and >= price should be able to buy just 98 of this item amount left from playerHash2.<br></p>
  
  <p>Here is the math I want to put into the query<br></p>
  
  <pre><code>VAR amountBuyerNeeds = (B.amount - B.bought)
  VAR amountSellerStock = (S.amount - S.sold)
  //update these values in the simulated cloned table data.
  B.bought = IF(amountBuyerNeeds &gt; amountSellerStock, (B.bought + amountSellerStock), B.amount)
  S.sold = IF(amountSellerStock &gt; amountBuyerNeeds, (S.sold + amountBuyerNeeds), S.amount)
  //to real row print out
  willBuyAmount = IF(amountBuyerNeeds &gt; amountSellerStock, amountSellerStock, amountBuyerNeeds)
  </code></pre>
  
  <p>Query ATM looks like this.</p>
  
  <pre><code>SELECT S.itemId AS sell_itemId,
         S.amount AS sell_amount,
         S.price AS sell_price,
         S.sold AS sell_sold,
         S.collected AS sell_collected,
         S.slot AS sell_slot,
         S.playerHash AS sell_playerHash,
         B.itemId AS buy_itemId,
         B.amount AS buy_amount,
         B.price AS buy_price,
         B.bought AS buy_bought,
         B.collected AS buy_collected,
         B.slot AS buy_slot,
         B.playerHash AS buy_playerHash
    FROM Buying AS B, 
         Selling AS S
  ON B.itemId = S.itemId 
  AND
  B.aborted = 0 
  AND
  S.aborted = 0 
  AND
  B.price &gt;= S.price 
  AND
  S.sold &lt; S.amount 
  AND
  B.bought &lt; B.amount
   ORDER BY B.price DESC
   LIMIT 100;
  </code></pre>