title: Transitive SQL query on same table
body: <p>Hey. consider d following table and data...</p>
  
  <pre><code>in_timestamp | out_timestamp | name  | in_id | out_id | in_server | out_server | status
  timestamp1   | timestamp2    | data1 |id1   | id2    | others-server1   | my-server1 | success
  timestamp2   | timestamp3    | data1 | id2   | id3    | my-server1   | my-server2 | success
  timestamp3   | timestamp4    | data1 | id3   | id4    | my-server2   | my-server3 | success
  timestamp4   | timestamp5    | data1 | id4   | id5    | my-server3   | others-server2 | success
  </code></pre>
  
  <ul>
  <li>the above data represent log of a execution flow of some data across servers. </li>
  <li>e.g. some data has flowed from some 'outside-server1' to bunch of 'my-servers' and finally to destined 'others-server2'. </li>
  </ul>
  
  <p>Question : </p>
  
  <p>1) I need to give this log in representable form to client where he doesn't need to know anything about the bunch of 'my-servers'. All i am supposed to give is timestamp of the data entered my infrastructure and when it left; drilling down to following info.</p>
  
  <pre><code>in_timestamp (of 'others_server1' to 'my-server1')
  out_timestamp (of 'my-server3' to 'others-server2')
  name 
  status
  </code></pre>
  
  <p>I want to write sql for the same! Can someone help? 
  NOTE : there might not be 3 'my-servers' all the time. It differs from situation to situation. e.g. there might be 4 'my-server'  involved for, say, data2!</p>
  
  <p>2) Are there any other alternatives to SQL? I mean stored procs/etc?</p>
  
  <p>3) Optimizations? (The records are huge in number! As of now, it is around 5 million a day. And we are supposed to show records that are upto a week old.)</p>
  
  <p>In advance, THANKS FOR THE HELP! :)</p>