fenway

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?

Everything is possible -- but we'll need far more information.

Queries against numerical columns are always faster.

Yeah, never do this.

InnoDB's an entirely different beast -- and if you're not used to working with it on at least 7 different levels, it's going to be very, very painful transition.

A sample of few hundred records would have sufficed....

Ok -- but repair from frm should work, too, AFAIK.

There isn't a more efficient way -- but yes, INNER JOIN wherever possible.

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

That's only becuase you're using INNER JOIN, not LEFT JOIN -- has nothing to do with the function.

I think you're asking two separate things. 1) use last_insert_id() to get back the ID so that you can use it in subsequent statements. 2) Use FK constraints if you're serious.

So, solved?

You can't delete the data AND not delete the data. Flag's aren't a problem.

Other post? Topic locked -- and don't double-post next time; that's a violation of the ToS.

MySQL evolved for simple web applications years ago -- and personally, I still think it's best suited for exactly that.

In general, as long as the math isn't used to search the database, you're ok.

Sorry, I know it's been a while -- can you post the create table statements and some insert statements so that I can re-create this scenario on my end?

Actually, I meant just rebuild the index file from the existing data -- though if you have a recent enough backup, your method works too.

The JOIN and comma operators changed precedence across mysql versions a while back -- that alone makes it evil.

Sounds like just the MYI file is an issue -- which is just the index data -- you should be able to rebuilt it without any data loss.

That's nota mysql error.

Then you need a sortorder column.

It just leads to all kinds of problems -- missing join conditions, precedence hell will left joins, the list is endless.