title: Field level comparision in MYSQL language
body: <p>I have a table as follows</p>
  
  <pre><code>ID CITY PARENTID
  1  VIZ     1
  2  HYD     1
  3  CHE     1
  6  MUM     3
  15 KOL     2
  </code></pre>
  
  <p>I need a SQL that first compares the ID field with each value in the field PARENTID and reverts back to ID and picks the corresponding CITY for that field.</p>
  
  <p>For Example, when ID value is 3, it checks its corresponding PARENTID which is 1 and then it should return the CITY value which is corresponding to 1 in the ID field which is VIZ.,</p>
  
  <p>One more example is for ID 6 and its corresponding PARENTID is 3 and it will again check the ID field for 3 and returns its corresponding CITY which is CHE.,</p>
  
  <p>Similarly for ID 15 the CITY should be HYD,</p>
  
  <p>Can this be captured in MYSQL using a SQL Query rather than using any anonymous Block or Stored Procedure??</p>
  
  <p>I created a SQL with SELF JOIN as below </p>
  
  <pre><code>    SELECT DISTINCT G.CITY FROM TBLGEOMARKET G,TBLGEOMARKET GM
      WHERE GM.ID = G.PARENTID;
  </code></pre>
  
  <p>TEST SQL:</p>
  
  <pre><code>    SELECT DISTINCT G.CITY FROM TBLGEOMARKET G,TBLGEOMARKET GM
      WHERE GM.ID = G.PARENTID AND GM.PARENTID = 15;
  </code></pre>
  
  <p>Here the Output is KOL which is not correct as its giving the City corresponding to ID rather than checking the PARENTID in ID and giving the result, which is HYD in our scenario.</p>
  
  <p>I'm missing some clause here, any information absolutely appreciated.</p>