0

I have a query I'm having trouble with that involves 3 tables described below. There are more fields that are immaterial to this query.

users:
  id
  display_name
matches:
  id
  league_seasons_id
  date_played
  winner_id
  loser_id
players:
  id
  match_id
  player_id
  nth_match
  division
  result   # values are "Win" or "Loss"
  end_hcp

I'm trying to produce output like this:

  Name        Hcp   Wins   Losses   High Run    HR Pct of Hcp
  Alan        705    12       2       27            3.83%
  Bill        815    10       4       30            3.68%
  Charlie     611     9       5       12            1.96%
  Denise      788     8       6       15            1.90%
  ...

The query below mostly works, except for the Hcp column. In the players table there are 2 records for each match, one with the winner's info and one with the loser's. In each record is the start_hcp and the end_hcp, i.e. the handicap that player had before playing the match and the new one based on the match.

I need the end_hcp from the very last match that each player played up until now (the date/time of the query). I'm guessing there's going to be a subquery involved but I haven't figured out what it is.

SELECT 
    p.division AS Division,
    u.display_name AS Name,
    p.end_hcp AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "Loss", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses
  • Thanks ypercube for the edits, I posted in a hurry before I ran out to get my great-grandkids from school. – John Biddle Oct 13 '17 at 19:04
  • 1
    Sounds like a "groupwise max" problem. See the tag I added. – Rick James Oct 13 '17 at 20:36
  • @Rick James Thanks for the reference to Groupwise max that does seem to be the type of problem I have and explains why my first attempts to solve it have been unsuccessful. The resolutions I have found, however, all refer to one table and my data is spread over two. I've found it very difficult to make this work. Can you assist further? – John Biddle Oct 17 '17 at 22:05
  • 1
    A possibility: Define a VIEW with your 2-table query, then use that view name in the place where you would have the table for doing the groupwise-max. – Rick James Oct 17 '17 at 22:39
0

I'm not a MySQL guy but what about something like the following (for end_hcp)?

Edit: based on syntax comment

SELECT 
    p.division AS Division,
    u.display_name AS Name,
    (select max(end_hcp) from lwljhb_lwl_players where player_id = p.player_id and match_id =(select max(p.match_id) from lwljhb_lwl_players where p.player_id = u.id))
 AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses
| improve this answer | |
  • I inserted your line in place of "p.end_hcp AS Handicap" but got a MySQL syntax error. Not sure if that'is what you were saying to do. I think you inserted your subquery in the wrong place. – John Biddle Oct 13 '17 at 19:09
  • OK, found a MySQL syntax checker. See if this gets you closer to your goal--so to speak... – Sting Oct 13 '17 at 19:31
  • Error: subroutine returns more than one row – John Biddle Oct 13 '17 at 20:12
  • I was afraid that was going to happen without me being able to see the data. Without providing the Limit 1 hack, try my next edit--above. I added player_id = p.player_id in one more additional place in the subquery. – Sting Oct 13 '17 at 20:32
  • Still getting the same error: subroutine returns more than one row. You edited my code, right, not just the one line showing the change below the code? Also, end_hcp appears twice in the query, nd you only replaced one of them. Is that right? – John Biddle Oct 13 '17 at 21:20
0

Based on a couple comments from @Rick James above I was able to find information on the Groupwise Max problem. Though no solution I found directly solved my issue of needing a second field from the record containing the max(nth_match) I was able to use the technique of making temp tables to get the data I needed and then joining them to my data tables to get my answer.

Here's the code if anyone's interested. Also, If someone sees a better way than making two temp tables I'd love to see it, I must have tried 20 different non-working ideas before this one.

DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1
SELECT player_id,
       max(nth_match) AS last_match
FROM `lwljhb_lwl_players` AS p INNER JOIN `lwljhb_lwl_matches` AS m
ON    p.match_id = m.id
WHERE m.playoff_round = 0 AND
      m.league_seasons_id = 9
GROUP BY p.player_id;

DROP TEMPORARY TABLE IF EXISTS temp2;
CREATE TEMPORARY TABLE temp2
SELECT p.player_id,
       t1.last_match,
       p.end_hcp
FROM `temp1` AS t1 INNER JOIN `lwljhb_lwl_players` AS p
ON t1.last_match = p.nth_match AND t1.player_id = p.player_id
GROUP BY p.player_id;

SELECT p.division AS Division,
       u.display_name AS Name,
       t2.end_hcp AS Handicap,
       count(if(result = "Win", 1, NULL)) AS Wins,
       count(if(result = "Loss", 1, NULL)) AS Losses,
       max(high_run) AS "High Run",
       concat(truncate((max(high_run) * 100 / t2.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM `lwljhb_lwl_players` AS p
INNER JOIN `lwljhb_users` AS u
INNER JOIN `lwljhb_lwl_matches` AS m
INNER JOIN `temp2` AS t2
ON p.match_id = m.id AND
   p.player_id = u.id AND
   t2.player_id = p.player_id
WHERE u.display_name <> "Bye" AND
      m.league_seasons_id = 9 AND
      p.division = "A" AND
      m.playoff_round = 0
GROUP BY u.display_name
ORDER BY Wins DESC, Losses, t2.end_hcp DESC;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.