fenway

Don't use abbreviations for column names -- that's just confusing. The lazy way is use to a use a GROUP by on assembly_number, then use MIN(tracking), and then count these. Perhaps a better way is to join a table for unique assembly_number to the base table on the first record in the index spanning these fields.

Then you can probably get away with latin1, which has support for those characters, too.

That still means you want a query to run periodically -- 5.1 has an event scheduler built-in.

I've no idea what you thought this had to do with MySQL. This topic has been moved to HTML Help. http://www.phpfreaks.com/forums/index.php?topic=311085.0

I don't understand -- you want to run the query every X, or the result to be based upon X?

Well, maybe I don't understand what you're trying to achieve.

I still don't see why you just can't join it directly and combine the ON conditions.

Why do you have a subquery in the right join?

Shift what around?

Yeah, don't.

There are no materialized views in mysql, so it's basically the same.

Only on the "right" table of any join.

This topic has been moved to PHP Coding Help. http://www.phpfreaks.com/forums/index.php?topic=310669.0

There's no "locking read" -- that's pessimistic locking that you'll have to build into your application.

What language are you storing?

Then you've got a php problem.

Now I'm really confused -- what are you trying to achieve here?

Drop the GROUP BY / aggregate part, make sure it's reasonable output

Of course... I simply assumed that's why distinct was there to begin with. That's what I get for not reading.

I don't understand what you mean...

Sorry, I'm very confused -- how does DISTINCT return more than one row here?

Use IN, not =.

Actually, you probably don't need derived tables -- try this (untested): SELECT DISTINCT s.dog FROM singles s INNER JOIN tournament t1 ON t1.enumber = s.event INNER JOIN tournament t2 ON t2.enumber = s.event where t1.date > t2.date and DATEDIFF( t1.date, t2.date ) > 4

OK, sorry it took so long. Now that I've played with it a bit, I don't think you need user variables -- I was thinking of rankings, and that's not actually what you want. Basically, if you join all the dog/dates to themselves, any time you get a condition where the dates differ by 4 days for a given dog, you want that dog back. If that's the case, this should work: select distinct t1.dog from ( SELECT s.dog,t.date FROM singles s INNER JOIN tournament t ON t.enumber = s.event ) t1 inner join ( SELECT s.dog,t.date FROM singles s INNER JOIN tournament t ON t.enumber = s.event ) t2 on ( t1.dog = t2.dog ) where t1.date > t2.date and DATEDIFF( t1.date, t2.date ) > 4 order by t1.dog, t1.date The date comparison in the where clause is simply to ensure the temporal order of the two dates, so that the DATEDIFF() will never be negative; and it neatly cuts down the number of rows to examine. Does that make sense?

Huh?