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

Question

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.

Bob Jarvis - Слава Україні · Accepted Answer · 2010-10-20 16:01:31Z

1

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.

edited Oct 20, 2010 at 16:01

answered Oct 20, 2010 at 15:34

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gaurav Over a year ago

Thanks a lot. This is exactly what I was looking for. But this has really increased the execution time now. I don't have an index on table though. What would be the best columns to create index on just by looking at the query? or using any other method?

Bob Jarvis - Слава Україні Over a year ago

@Gaurav: please add the definitions of these tables to your original question. It's hard to make recommendations without understanding what the tables look like. Thanks.

Ken Richards · Accepted Answer · 2010-10-20 15:44:52Z

0

You should be able to left join to a sub-select so you only run the query once. Then you can do a join condition to pull out the maximum for the ID on each record as shown below:

SELECT id,
       COUNT(DISTINCT archdetails.compname)
FROM   svn1,
       svn3,
       archdetails
LEFT JOIN (
        SELECT id, MAX(date) AS MaximumDate
          FROM   svn1
          WHERE  TYPE = 'Bug'
          GROUP BY id
       ) AS MaxDate ON MaxDate.id = svn1.id
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 <= MaxDate.MaximumDate
                      AND edate >= MaxDate.MaximumDate
                  )
               OR (
                      sdate >= startdate
                      AND edate <= MaxDate.MaximumDate
                  )
           )
GROUP BY
       id LIMIT 0,
       40;

answered Oct 20, 2010 at 15:44

Ken Richards

3,0512 gold badges22 silver badges23 bronze badges

1 Comment

Gaurav Over a year ago

It is unable to recognize svn1.id in MaxDate.id = svn1.id. I tried changing it to FROM svn1 t and MaxDate.id = t.id but that doesnot seem to work either. >Error:Unknown column 't.id' in 'on clause'

Jake Sylvestre · Accepted Answer · 2016-11-17 23:58:10Z

0

Try using alias, something like this should work:

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

edited Nov 17, 2016 at 23:58

Jake Sylvestre

9433 gold badges14 silver badges41 bronze badges

answered Oct 20, 2010 at 15:29

Yasen Zhelev

4,0454 gold badges33 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related