title: writing a sql query in MySQL with subquery on the same table
body: <p>I have a table svn1:</p>
  <blockquote>
  <p>id | date | startdate</p>
  <p>23 2002-12-04  2000-11-11<br />
  23 2004-08-19 2005-09-10<br />
  23 2002-09-09 2004-08-23</p>
  </blockquote>
  <p><code>select id,startdate from svn1 where startdate&gt;=(select max(date) from svn1 where id=svn1.id);</code></p>
  <p>Now the problem is how do I let know the subquery to match id with the id in the outer query. Obviously id=svn1.id wont work. Thanks!</p>
  <blockquote>
  <p>If you have the time to read more:</p>
  </blockquote>
  <p>This really is a simplified version of asking what I really am trying to do here. my actual query is something like this</p>
  <pre><code>    select 
            id, count(distinct archdetails.compname) 
      from 
            svn1,svn3,archdetails 
      where 
            svn1.name='ant' 
        and svn3.name='ant' 
        and archdetails.name='ant' 
        and type='Bug' 
        and svn1.revno=svn3.revno 
        and svn3.compname=archdetails.compname 
        and 
            ( 
              (startdate&gt;=sdate and startdate&lt;=edate) 
              or 
              (
               sdate&lt;=(select max(date) from svn1 where type='Bug' and id=svn1.id) 
               and 
               edate&gt;=(select max(date) from svn1 where type='Bug' and id=svn1.id)
              ) 
              or 
              (
               sdate&gt;=startdate 
               and 
               edate&lt;=(select max(date) from svn1 where type='Bug' and id=svn1.id)
              ) 
            )  
        group by id LIMIT 0,40;
  </code></pre>
  <p>As you notice <code>select max(date) from svn1 where type='Bug' and id=svn1.id</code> has to be calculated many times.</p>
  <p>Can I just calculate this once and store it using <code>AS</code> and then use that variable later. Main problem is to correct <code>id=svn1.id</code> so as to correctly equate it to the id in the outer table.</p>