title: mysql - query to lookup multiple records
body: <p>I have a single mysql table. Query should lookup prev_id multiple times till the insertimestamp is between cuurent and previous week and get the rows to columns.</p>
  
  <pre><code> ID | inserttimestamp| prev_id  | category  
   -----------------------------
   5  | 2017-06-08     |     4    | Level456
   4  | 2017-06-05     |     3    | Level241
   3  | 2017-05-31     |     2    | Level456
   2  | 2017-05-28     |     1    | Level247
   1  | 2017-05-27     |          | Level231
  </code></pre>
  
  <p>The result should be something like this,</p>
  
  <pre><code> ID | inserttimestamp| prev_id  | category    | id1  | id1_category  | id2   | id2_category   |
   --------------------------------------------------------------------------------------------
   5  | 2017-06-08     |     4    | Level456    |  4   |  Level 241    |   3   |   Level456     |
  </code></pre>
  
  <p>In this case as you see it stopped to id 3  because id 2 and 1 are not falling under previous week.</p>
  
  <p>Use Case: To find out how many level upgrade/downgrade happened since previous week</p>
  
  <p>For the above records its  Levelupgrade - 1 , LevelDowngrade -1</p>
  
  <p>Any help would be highly appreciated. Thanks in advance.</p>