I am relatively new with mysql and php. I have developed a hockey stat db. Until now, I have been doing pretty basic queries and reporting of the stats. I want to do a little more advanced query now.
I have a table that records which players were on the ice (shows as a "fk_pp1_id" - "fk_pp5_id") when a goal is scored. here is the table:
pt_id | fk_gf_id | fk_pp1_id | fk_pp2_id | fk_pp3_id | fk_pp4_id | fk_pp5_id 1 | 1 | 19 | 20 | 68 | 90 | 97 2 | 2 | 1 | 19 | 20 | 56 | 91 3 | 3 | 1 | 56 | 88 | 91 | 93 4 | 4 | 1 | 19 | 64 | 88 | NULL 5 | 5 | 19 | 62 | 68 | 88 | 97 6 | 6 | 55 | 19 | 20 | 45 | 62 7 | 7 | 1 | 19 | 20 | 56 | 61 8 | 8 | 65 | 68 | 90 | 93 | 97 9 | 9 | 19 | 20 | 45 | 55 | 62 10 | 10 | 1 | 19 | 20 | 56 | 61 11 | 11 | 1 | 19 | 20 | 56 | 61 12 | 12 | 19 | 20 | 68 | 90 | 97 13 | 13 | 19 | 20 | 68 | 90 | 97 14 | 14 | 19 | 20 | 55 | 62 | 91 15 | 15 | 1 | 56 | 61 | 64 | 88 16 | 16 | 1 | 56 | 61 | 64 | 88 17 | 17 | 1 | 19 | 20 | 56 | 61 18 | 18 | 1 | 19 | 20 | 56 | 61 19 | 19 | 1 | 65 | 68 | 93 | 97
I want to do several queries:
- Show which of the five players were together on the ice most often when a goal was scored.
- Select say 2 players and show which other players were on the ice most often with them when a goal was scored.
I was able to write a query which partially accomplishes query #1 above.
SELECT fk_pp1_id, fk_pp2_id, fk_pp3_id, fk_pp4_id, fk_pp5_id, count(*) FROM TABLE1 group by fk_pp1_id, fk_pp2_id, fk_pp3_id, fk_pp4_id, fk_pp5_id
Here are the results:
fk_pp1_id fk_pp2_id fk_pp3_id fk_pp4_id fk_pp5_id count(*) 1 19 20 56 61 4 1 19 20 56 91 1 1 19 64 88 (null) 1 1 56 61 64 88 2 1 56 88 91 93 1 1 65 68 93 97 1 19 1 20 56 61 1 19 20 45 55 62 1 19 20 55 62 91 1 19 20 68 90 97 3 19 62 68 88 97 1 55 19 20 45 62 1 65 68 90 93 97 1 4
See this sqlfiddle:
http://sqlfiddle.com/#!9/e3f5f/1
This seems to work at first, but I realized this query, as written, is sensitive to the order in which the players are listed. That is to say a row with: 1, 19, 20, 68, 90 will not match 19, 1, 20, 68, 90
So to fix this problem, I feel like I have a couple options:
Ensure the data is input into the table in numerical order
Re-write the query so the order of the data in the table doesn't matter
Make the resulting query a sub-query to another query that first orders the column (left to right) in numerical order.
Change the schema to record/store the data in a better way
1, I can do, but would prefer to have the query be fool-proof.
2 or 3 I prefer, but don't know how to do either.
4, I don't know how to do and is least desirable as I already have some complex queries against this table that would need to be totally re-written.
Am i going about this in the wrong way or is there a solution??
Thanks for your help
UPDATE - OK I (hopefully) better normalized the data in the table. Thanks @strawberry. Now my table has a column for the goal_id (foreign key) and a column for the player_id (another foreign key) that was on the ice at the time the goal was scored.
Here is the new fiddle: http://sqlfiddle.com/#!9/39e5a
I can easily get the one player who was on the ice most when goals are scored, but I can't get my mind around how to find the occurrences of a group of players who were on the ice together. For example, how many times were a group of 5 players on the ice together. Then from there, how often a group of 2 players were on the ice together with the 3 other players.
Any other clues???