title: Searching a Record from Middle of the Table
body: <p>I am facing issue in finding the data from MySql table.</p>
  
  <p><strong>Table A:</strong></p>
  
  <pre><code>+-------+-------------+------+-----+-------------------+-------+
  | ID    | Table_b_fk  |Value | age | name              | score |
  +-------+-------------+------+-----+-------------------+-------+
  | 01    | 01          | 255  |  21 | Tom               |  65   | 
  | 02    | 02          | 36   |  20 | Peter             |  95   | 
  | 03    | 03          | 25   |  22 | John              |  65   | 
  | 04    | 04          | 36   |  20 | Bond              |  95   |&lt;&lt;----First 
  | 05    | 05          | 258  |  22 | Smith             |  65   | 
  | 06    | 06          | 420  |  20 | Robert            |  95   | 
  | 07    | 07          | 258  |  22 | Nisarg Patel      |  65   | 
  | 08    | 08          | 16   |  21 | Peter             |  95   | 
  | 09    | 09          | 25   |  23 | J0k               |  65   | 
  | 10    | 10          | 36   |  22 | Prodigitalson     |  95   | 
  | 11    | 11          | 205  |  22 | Silver            |  65   |&lt;&lt;----Next
  | 12    | 12          | 37   |  20 | Json              |  95   | 
  | 13    | 13          | 285  |  23 | Villa             |  65   | 
  | 14    | 14          | 36   |  22 | Parker            |  95   | 
  +-------+-------------+------+-----+-------------------+-------+
  </code></pre>
  
  <p><strong>Table B:</strong></p>
  
  <pre><code>+-------+-------------+------+-----+-------------------+-------+
  | ID    | Result      | M1   |  M2 | name              | score |
  +-------+-------------+------+-----+-------------------+-------+
  | 01    | Pass        | 30   |  26 | Tom               |  65   | 
  | 02    | Pass        | 30   |  20 | Peter             |  95   | 
  | 03    | Pass        | 25   |  60 | John              |  65   | 
  | 04    | Pass        | 100  | 100 | Bond              |  95   |&lt;&lt;----First 
  | 05    | Pass        | 55   |  65 | Smith             |  65   | 
  | 06    | Pass        | 80   |  95 | Robert            |  95   | 
  | 07    | Pass        | 65   |  75 | Nisarg Patel      |  65   | 
  | 08    | Pass        | 56   |  71 | Peter             |  95   | 
  | 09    | Pass        | 90   |  96 | J0k               |  65   | 
  | 10    | Pass        | 96   |  96 | Prodigitalson     |  95   | 
  | 11    | Pass        | 100  | 100 | Silver            |  65   |&lt;&lt;----Next
  | 12    | Pass        | 47   |  92 | Json              |  95   | 
  | 13    | Pass        | 82   |  73 | Villa             |  65   | 
  | 14    | Pass        | 86   |  72 | Parker            |  95   | 
  +-------+-------------+------+-----+-------------------+-------+
  </code></pre>
  
  <p>I am joining TableA &amp; TableB, where in TableA <code>Table_b_fk</code> is foreign key to TableB.</p>
  
  <p>I am finding the record which matches the TableB column <code>M1 &amp; M2 = 100</code>.</p>
  
  <p><strong>My Scenario: 1</strong></p>
  
  <p>I know the first occurrence of the match record ID : <code>04</code> in <code>TableA</code>. I want to do a search to find the next record with <code>M1 &amp; M2 = 100</code>. (Record Id-11) But the search should not start from <strong>01</strong>. It should start from the last found record Id. That is from <strong>O4</strong> the search should start to find the next occurrence of the record.</p>
  
  <p>My Try:
  I tried to find using Limit but it didn't help me to find. Can some one help me in this?</p>
  
  <p><strong>Edit: 1</strong></p>
  
  <p><strong>My Scenario: 2</strong>
  In my second case my TableB has repeated Data and the ID was foreign in TableA. How can I fins the record. ? with the matching ID/M1/M2 values: I found a solution for that. I just want to find the Current Record FOREIGN KEY and Check for the next occurrence of the record in the same table and I can get the next record rite?</p>
  
  <p>In this case my TableB record are not as same as TableA records. In other words my TableA records will point to tableA. Many-to-one. Is this rite?</p>
  
  <p><strong>Edit: 2</strong></p>
  
  <p>Thanks for all your efforts and knowledge I found a solution for scenario:2 check it:</p>
  
  <pre><code>CREATE TABLE TableB (
    ID Int,
    Result VARCHAR(20),
    M1 INT,
    M2 INT,
    name VARCHAR(20),
    Score INT);
  
  INSERT INTO TableB VALUES
  ( 11    , 'Pass'        , 30   ,  26 , 'Tom'               ,  65   ), 
  ( 13    , 'Pass'        , 30   ,  20 , 'Peter'             ,  95   ),
  ( 80    , 'Pass'        , 25   ,  60 , 'John'              ,  65   ),
  ( 81    , 'Pass'        , 100  , 100 , 'Bond'              ,  95   ),
  ( 90    , 'Pass'        , 55   ,  65 , 'Smith'             ,  65   ),
  ( 96    , 'Pass'        , 80   ,  95 , 'Robert'            ,  95   ),
  ( 97    , 'Pass'        , 65   ,  75 , 'Nisarg Patel'      ,  65   ),
  ( 98    , 'Pass'        , 56   ,  71 , 'Peter'             ,  95   ),
  ( 99    , 'Pass'        , 90   ,  96 , 'J0k'               ,  65   ),
  ( 100    , 'Pass'        , 96   ,  96 , 'Prodigitalson'     ,  95   ),
  ( 101    , 'Pass'        , 10  , 10 , 'Silver'            ,  65   ),
  ( 103    , 'Pass'        , 47   ,  92 , 'Json'              ,  95   ),
  ( 201    , 'Pass'        , 82   ,  73 , 'Villa'             ,  65   ), 
  ( 222    , 'Pass'        , 86   ,  72 , 'Parker'            ,  95   )
  ;
  
  
  CREATE TABLE TableA
      (`ID` int, `Table_b_fk` int, `Value` int, `age` int, `name` varchar(13), `score` int)
  ;
  
  INSERT INTO TableA
      (`ID`, `Table_b_fk`, `Value`, `age`, `name`, `score`)
  VALUES
      (01, 11, 255, 21, 'Tom', 65),
      (02, 81, 36, 20, 'Peter', 95),
      (03, 80, 25, 22, 'John', 65),
      (04, 97, 36, 20, 'Bond', 95),
      (05, 81, 258, 22, 'Smith', 65),
      (06, 06, 420, 20, 'Robert', 95),
      (07, 81, 258, 22, 'Nisarg Patel', 65),
      (08, 08, 16, 21, 'Peter', 95),
      (09, 96, 25, 23, 'J0k', 65),
      (10, 101, 36, 22, 'Prodigitalson', 95),
      (11, 222, 205, 22, 'Silver', 65),
      (12, 12, 37, 20, 'Json', 95),
      (13, 201, 285, 23, 'Villa', 65),
      (14, 101, 36, 22, 'Parker', 95)
  ;
  </code></pre>
  
  <p>Solution for that is:</p>
  
  <pre><code>SELECT a.id  
  FROM TableB b
  INNER JOIN TableA a 
  ON a.Table_b_fk = b.id
  WHERE M1 = 100 and M2 = 100  AND a.ID&gt;4 limit 1
  </code></pre>
  
  <p>where the limit just limits the next record.. (answer is 5).</p>
  
  <p>I case of <code>Doctrine 2</code>: Use the below Query code.</p>
  
  <pre><code>$qry = $this-&gt;manager()-&gt;createQueryBuilder()
          -&gt;select(array('e', 's'))
          -&gt;from('YOUR_DOMAIN', 'e')
          -&gt;Join('e.table_b_k', 's')
          -&gt;where("s.m1 = ?", $valueone)
          -&gt;andwhere("s.m2 = ?", $valuetwo)
          -&gt;andwhere("e.id &gt; ?", $currentrecord)
          -&gt;setMaxResult(1);
  </code></pre>
  
  <p>Note: <code>YOUR_DOMAIN</code> here is the TableA. TableA and TableB should be joined through the Mapping so we dont need to Join/Reference int he Query.. directly as TableB. The will be done by the second line Join in the above example. It is not tested as of now.</p>