Subquery help needed

[ViperSBT]

SELECT r.club, MIN(r.besttime) as time, c.cname, t.date
FROM racing r
JOIN clubs c ON r.club = c.cnumber
INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999
GROUP BY r.club
HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4
ORDER BY time, c.cname

 

The above query generates a list of clubs that have participated in more than two events that are greater than 4 days apart over the last 18 months.  However, I need to limit the MIN(r.besttime)  to only look at the last 12 months.  Any thoughts?

[ViperSBT]

Thought I would try to clarify a little further where I need to go with this query.  I need to take the results of this query and then identify the besttime of each of the clubs since the date of 2007-08-01.

[fenway]

Why can't you put this in the having clause too?

[ViperSBT]

When I have tried doing that it limits the first date selection.

[ViperSBT]

So it looks like I can only limit this to one date set...

[ViperSBT]

Is there not a way that I could pull my query that gets the MIN(besttime) from the last 12 months using the query that figures out the clubs that have participated in more than two events seperated by at least 4 days?

[ViperSBT]

I tried this, which seems to work and is correct, but it takes about 10 minutes to process:

SELECT r.club, MIN(r.besttime) as time, c.cname, t.date
FROM racing r
INNER JOIN clubs c ON r.club = c.cnumber
INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-08-01' && r.besttime < 99.999
WHERE r.club IN
(SELECT r.club
FROM racing r
INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999
GROUP BY r.club
HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4)
GROUP BY r.club
ORDER BY time, c.cname

[fenway]

Could you post the EXPLAIN of that query?

 

Also, why not JOIN this dervied tables?

[ViperSBT]

I don't know why I am not "JOIN the derived tables", I am still very green at all this and only know enough to be dangerous.

 

As for the explanation of the query:

 

SELECT r.club, MIN(r.besttime) as time, c.cname, t.date

Here I am looking for the club id, the fastest time that the club has ever run, the club name, and the date of the event that they ran that time.

 

INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-08-01' && r.besttime < 99.999

This join is to limit the scope of the club's fastest time to a period of the last 12 months.  So, even though I may have times for them stretching back 3 years, I only want their fastest time during the last 12 months.  In production I am using a variable here instead of a fixed date.

 

WHERE r.club IN
(SELECT r.club
FROM racing r
INNER JOIN tournament t ON t.enumber = r.event && t.date >= '2007-01-01' && r.besttime < 99.999
GROUP BY r.club
HAVING DATEDIFF( MAX( date ) , MIN( t.date ) ) >4)

This part of the query is doing a check against all the entries I have in the racing table to only provide me with those clubs that have entered more than one event during the last 18 months (again in production I am using a variable) and those events had to be more than 4 days apart from each other.

 

So in plain language I am looking for all clubs that have competed in more than two events, greater than 4 days apart, during the last 18 months and need to know the fastest time they ran during the last 12 months.

[fenway]

I understand what you're looking for... but JOINs don't work well with ranges.

[ViperSBT]

OK, I'll buy that JOINs don't work well with ranges, but how else would you do this query?

[fenway]

OK, I'll buy that JOINs don't work well with ranges, but how else would you do this query?

You write a bunch of individual query, and then then join them together... "derived" tables, as it were.  I don't have time now, maybe later on this evening I will try and work with your schema.

[ViperSBT]

Fenway, any luck with this?  I kinda understand where you are going with this, but not sure how to go about putting it into action.  I really do appreciate the help.

[fenway]

I'm confused again... sorry, it's been a while.

 

This gets your clubs for 18 months (though I don't see where the 2 event criterion is satisfied):

 

SELECT r.club, DATEDIFF( MAX( date ) , MIN( t.date ) ) AS diff

FROM racing r

INNER JOIN tournament t ON ( t.enumber = r.event )

WHERE t.date >= '2007-01-01' AND r.besttime < 99.999

GROUP BY r.club

HAVING diff > 4

 

Now, the next step you want to do is a) limit this to 12 months and, more importantly, b) just get the min() of some column?

[ViperSBT]

Yes, you are on track.  The 2 events criterion is satisfied by ensuring that the (diff)erence between event dates is >4.

 

Once we have the list of clubs for 18 months, we need to analyze the best times of that list but only looking at the last 12 months for the times.

[fenway]

Why not this?

 

SELECT club, MIN(r.besttime) FROM

( SELECT r.club, r.besttime, r.date, DATEDIFF( MAX( r.date ) , MIN( t.date ) ) AS diff

FROM racing r

INNER JOIN tournament t ON ( t.enumber = r.event )

WHERE t.date >= '2007-01-01' AND r.besttime < 99.999

GROUP BY r.club

HAVING diff > 4 ) AS sub

WHERE r.date >= '2007-08-01'

GROUP BY club