Geek Answers Handbook

Writing SQL query in MySQL with a subquery in the same table

I have a svn1 table:

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 can I tell the subquery to match the id with the id in the outer request. Obviously id = svn1.id does not work. Thanks!

If you have time to learn more:

This is a really simplified version of the question of what I'm really trying to do here. my actual request 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 noticed, select max(date) from svn1 where type='Bug' and id=svn1.id has to be calculated many times.

Is it possible to simply calculate this once and save it with AS , and then use this variable later. The main problem is to fix id=svn1.id to correctly equate it to id in an external table.

+4
source share
3 answers

I'm not sure that you can exclude the repetition of the subquery, but the subquery can refer to the main query if you use a table alias, as shown below:

 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.

+1
source

Try using an 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);

0
source

You can go left to join the sub-select so that you only run the query once. You can then fulfill the join condition to get the maximum for the identifier in 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;
0
source

More articles:


All Articles