Gaurav Gaurav - 22 days ago 8
SQL Question

writing a sql query in MySQL with subquery on the same table

I have a table svn1:


id | date | startdate

23 2002-12-04 2000-11-11

23 2004-08-19 2005-09-10

23 2002-09-09 2004-08-23


select id,startdate from svn1 where startdate>=(select max(date) from svn1 where id=svn1.id);


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!


If you have the time to read more:


This really is a simplified version of asking what I really am trying to do here. my actual query is something like this

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>=sdate and startdate<=edate)
or
(
sdate<=(select max(date) from svn1 where type='Bug' and id=svn1.id)
and
edate>=(select max(date) from svn1 where type='Bug' and id=svn1.id)
)
or
(
sdate>=startdate
and
edate<=(select max(date) from svn1 where type='Bug' and id=svn1.id)
)
)
group by id LIMIT 0,40;


As you notice
select max(date) from svn1 where type='Bug' and id=svn1.id
has to be calculated many times.

Can I just calculate this once and store it using
AS
and then use that variable later. Main problem is to correct
id=svn1.id
so as to correctly equate it to the id in the outer table.

Answer

I'm not sure you can eliminate the repetition of the subquery, but the subquery can reference the main query if you use a table alias, as in the following:

select id,
       count(distinct archdetails.compname)
from svn1 s1,
     svn3 s3,
     archdetails a
where s1.name='ant' and
      s3.name='ant' and
      a.name='ant' and
      type='Bug' and
      s1.revno=s3.revno and
      s3.compname = a.compname and
      ( (startdate >= sdate and startdate<=edate) or
        (sdate <= (select max(date)
                     from svn1
                     where type='Bug' and
                           id=s1.id and
         edate>=(select max(date)
                   from svn1
                   where type='Bug' and
                   id=s1.id)) or
        (sdate >= startdate and edate<=(select max(date)
                                          from svn1
                                          where type='Bug' and
                                          id=s1.id)) )
group by id LIMIT 0,40;

Share and enjoy.