How to select rows based on values ​​in other rows

Question

I have a database full of sports results. I would like to select some results based on some characteristics of previous results. Here is the database structure:

CREATE TABLE `results` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `date` DATETIME NOT NULL , `home_score` INT NOT NULL , `away_score` INT NOT NULL , `home_team_id` INT NOT NULL , `away_team_id` INT NOT NULL ); 

So, I would like to make queries such as “find results when the team won two of their previous home games”, that is, for a certain order home_team_id by date, and then select each row, where in the previous two lines home_score> away_score,

I know this is a bit complicated, so any pointers on how to solve this problem will be widely appreciated. I currently have a version in PHP (selects all rows and then executes this type of query), but the performance is very slow and it uses a huge amount of memory (the database contains more than 20,000 rows).

EDIT: Thanks for a couple of neat answers. Ideally, however, I would like to be able to run queries across all columns, rather than just looking at W, D or L. A more difficult example would be to “find all the results when the home team won each of their five previous game houses, at least at least two goals, and the away team lost at least one goal to each of their away games. "

Answer 1

If you want this to be good, you should link sequential results in a table, for example. have a prev_result_id column that you set to the previous result whenever you insert a new result. Thus, you can solve the problem as follows:

 SELECT * FROM (SELECT * FROM results WHERE home_team_id = ID ORDER BY date DESC LIMIT 1) r1 INNER JOIN results r2 ON r2.id = r1.prev_result_id WHERE r1.home_score > r1.away_score AND r2.home_score > r2.away_score 

EDIT. To improve this, you can also have the next_result_id field (which is indexed) and have NULL for it for the last result (since there is no next result yet). This way you can get rid of the subquery in FROM, since you don’t have to specify a date at all:

 SELECT * FROM results r1 INNER JOIN results r2 ON r2.id = r1.prev_result_id WHERE r1.home_team_id = ID AND r1.next_result_id IS NULL AND r1.home_score > r1.away_score AND r2.home_score > r2.away_score 

This is especially good if you want to find ALL teams that have won their previous matches, because now you can simply omit r1.home_team_id = ID from the where clause, and it should give you one line for each team that won their previous two matches .

Answer 2

Depending on the full range of queries you need, I might consider denormalizing. for example, add columns such as

 home_performance = 'WWDLWW' away_performance = 'WWLLWL' overall_performance = 'WWWWDLLLWWWL' 

Then for special requests, for example, for their last 2 home games, you can answer by completing

 home_performance LIKE '%WW' 

Or won 4 in a row, there will be overall_performance LIKE '%WWWW%'

These derived columns can be populated with GROUP_CONCAT