CODEWITHSUNDEEP
mysqlsqlpdo
jidic
jidic

Reputation: 229

Sub-Query doesn't return expected result

I'm trying to get the season.id which have the maximum date for a match. The query is this:

SELECT s.id AS season_id,
s.name AS season_name,
s.competition_id AS competition_id
FROM competition_seasons s
INNER JOIN competition_rounds r ON r.season_id = s.id
INNER JOIN `match` m ON m.round_id = r.id
WHERE s.competition_id = 89
AND m.datetime = (SELECT MAX(m.datetime) FROM `match` m WHERE m.round_id = r.id)
GROUP BY s.id

as you can see I have a table called competition_seasons which contains all the seasons available for a competition, on this table I get all the information of the competition, in this case the competition have the id 89. Later I get all the rounds available for the season of the competition through the table competition_rounds, a match is played on a round, so I use INNER JOIN on the match table because I need to get only the rounds that have matches.

The competition 89 have different season with different rounds, the season are for example:

the query should return 2017/2018 because is the last season which have the match with the MAX(m.datetime) this field indicate when the match is played, a match played on the season 2017/2018 means that have a date that can start from year 2017 and end to 2018 (so within the two years of the season).

The result of the query instead is 2011, which have absolutely no sense.

What I did wrong in the query?

competition_seasons

| id | name       | competition_id
 1     2017/2018       89
 2     2016/2017       89
 3     2015/2016       89

competition_rounds

| id | name        | season_id
 1     First           1
 2     Second          1
 3     First           2
 4     Second          2
 5     First           3

match

|id | datetime           |  round_id
  1   2018-03-08 00:00:0    1
  2   2017-09-10 20:30:0    1
  3   2017-04-18 15:30:0    3
  4   2016-03-08 00:00:0    3
  5   2015-04-08 00:00:0    4
  6   2015-05-08 00:00:0    5

expected result: season_id = 1 because the match of this season have the field datetime greater than the previous seasons.

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I'm thinking ORDER BY and LIMIT would work:

SELECT s.id AS season_id, s.name AS season_name,
       s.competition_id AS competition_id
FROM competition_seasons s INNER JOIN
     competition_rounds r
     ON r.season_id = s.id INNER JOIN 
     `match` m
     ON m.round_id = r.id
WHERE s.competition_id = 89
ORDER BY m.datetime DESC
LIMIT 1;

Upvotes: 1

Gonzalo Lorieto
Gonzalo Lorieto

Reputation: 645

Try this:

SELECT s.id AS season_id,
s.name AS season_name,
s.competition_id AS competition_id
FROM competition_seasons s
INNER JOIN competition_rounds r ON r.season_id = s.id
INNER JOIN `match` m ON m.round_id = r.id
WHERE s.competition_id = 89
AND m.datetime = (SELECT MAX(m1.datetime) FROM `match` m1
                  INNER JOIN competition_rounds r1 
                  ON m1.round_id = r1.id
                  INNER JOIN competition_seasons s1
                  ON r1.season_id = s1.id
                  AND s1.competition_id = 89)

Upvotes: 0

Related Questions