CODEWITHSUNDEEP
mysqlsqldatabasesubquery
xms
xms

Reputation: 437

MySQL subquery does not give me the highest value with MAX or ORDER BY LIMIT 1

I have problems with my MySQL query. Its subquery does not give the highest value of last.id. 

SELECT rounds.winners, rounds.losers
FROM players
INNER JOIN teams ON teams.id = players.ilmo_id
INNER JOIN status AS first ON first.id = players.status_id
LEFT JOIN matches ON matches.chart_id = 12
  AND matches.id = (
    SELECT  last.id
    FROM    matches AS last
    WHERE   (last.player1_id = players.id
            OR last.player2_id = players.id
            OR last.player3_id = players.id
            OR last.player4_id = players.id)
            ORDER BY last.id DESC
            LIMIT 1
        )

 JOIN charts ON charts.id = matches.chart_id
 JOIN places ON charts.template_id = places.template_id AND places.id = matches.place_id
 JOIN templates ON places.template_id = templates.id
 JOIN rounds ON places.round_id = rounds.id
 WHERE players.comp_id = 12

I also have tried this way, but this does not work:

SELECT rounds.winners, rounds.losers
FROM players
INNER JOIN teams ON teams.id = players.ilmo_id
INNER JOIN status AS first ON first.id = players.status_id
LEFT JOIN matches ON matches.chart_id = 12
  AND matches.id = (
    SELECT  MAX(last.id)
    FROM    matches AS last
    WHERE   (last.player1_id = players.id
            OR last.player2_id = players.id
            OR last.player3_id = players.id
            OR last.player4_id = players.id)
        )

 JOIN charts ON charts.id = matches.chart_id
 JOIN places ON charts.template_id = places.template_id AND places.id = matches.place_id
 JOIN templates ON places.template_id = templates.id
 JOIN rounds ON places.round_id = rounds.id
 WHERE players.comp_id = 12

Edit:

Here is the latest version which seems to work OK.

SELECT rounds.winners, rounds.losers
FROM players
INNER JOIN teams ON teams.id = players.ilmo_id
INNER JOIN status AS first ON first.id = players.status_id
LEFT JOIN matches ON matches.chart_id = 12
  AND matches.id = (
    SELECT  last.id
    FROM    matches AS last
    WHERE   last.chart_id = 12 /* modified */
            AND (last.player1_id = players.id
            OR last.player2_id = players.id
            OR last.player3_id = players.id
            OR last.player4_id = players.id)
            ORDER BY last.place_id DESC /* modified */
            LIMIT 1
        )

 JOIN charts ON charts.id = matches.chart_id
 JOIN places ON charts.template_id = places.template_id AND places.id = matches.place_id
 JOIN templates ON places.template_id = templates.id
 JOIN rounds ON places.round_id = rounds.id
 WHERE players.comp_id = 12

Upvotes: 0

Views: 47

Answers (1)

LAS
LAS

Reputation: 819

SELECT rounds.winners, rounds.losers
FROM players
INNER JOIN teams ON teams.id = players.ilmo_id
INNER JOIN status AS first ON first.id = players.status_id
LEFT JOIN matches ON matches.id = (
    SELECT  max(last.id)
    FROM    matches AS last
    WHERE   last.chart_id = 12 /* modified */
            AND (last.player1_id = players.id
            OR last.player2_id = players.id
            OR last.player3_id = players.id
            OR last.player4_id = players.id)
        )
 JOIN charts ON charts.id = matches.chart_id
 JOIN places ON charts.template_id = places.template_id AND places.id = matches.place_id
 JOIN templates ON places.template_id = templates.id
 JOIN rounds ON places.round_id = rounds.id
 WHERE players.comp_id = 12

Upvotes: 1

Related Questions