title: MySQL SELECT from multiple tables, multiple GROUP BY and group_concat?
body: <p>I have three tables which I want to query in MySQ. As follows:</p>
  
  <pre><code>**Table: Leaderboard**
  Name  | Score
  ------------
  James | 1
  Steve | 2
  Dave  | 5
  
  **Table: Actions**
  Name  | Action       | Time
  ----------------------------
  James | Ate an apple | 01:00
  James | Kicked a dog | 02:00
  Steve | Ate a dog    | 03:00
  Steve | Kicked a hen | 01:00
  Dave  | died         | 02:00
  
  **Table: Items**
  Name  | Item         | Time
  ----------------------------
  James | Chainsaw     | 01:00
  James | Hammer       | 01:05
  James | Crowbar      | 01:10
  Steve | Hammer       | 02:00
  Steve | Egg          | 01:05
  Dave  | Egg          | 01:05
  </code></pre>
  
  <p>I need a query which selects each player (ORDER BY Leaderboard.score DESC) and selects their latest Action WHERE Actions.action LIKE 'Ate %', and then gives all Items.Item ORDER BY Time DESC</p>
  
  <p>So for example, the output would look like this</p>
  
  <pre><code>**Output**
  Name   | Latest_Action | Items
  Steve  | Ate a dog     | Hammer, Egg
  James  | Ate an apple  | Crowbar, Hammer, Chainsaw
  </code></pre>
  
  <p>So far I have tried the following query but it returns each Item multiple times in the group_concat</p>
  
  <pre><code>SELECT Leaderboard.Name, Actions.*, group_concat(Items.Item)
  FROM Leaderboard, Actions, Items
  WHERE Items.Name = Actions.Name
    AND Actions.Action LIKE 'Ate %'
    AND Actions.Name IN (SELECT Name FROM Leaderboard ORDER BY SCORE DESC)
  GROUP BY Leaderboard.name
  </code></pre>
  
  <p>Any help much appreciated!</p>