Databases
StackOverflow2013
Postsdbo
POThree joins with aggregate functions issue
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POThree joins with aggregate functions issue
    text
    copied!<p>I have three tables (MySQL):</p> <pre><code>matches: ___________________________ |match_static_id| team_name | |_______________|___________| | 1 | Italy | | 2 | France | |_______________|___________| users: ___________________________ |user_id | username | |_______________|___________| | 1 | Dolly | | 2 | Didi | |_______________|___________| forum: _____________________________________________________________ |match_static_id| comment | timestamp | user_id | |_______________|___________|______________________|__________| | 1 | Hi | 2013-07-10 12:15:03 | 2 | | 1 | Hello | 2013-07-09 12:14:44 | 1 | |_______________|___________|______________________|__________| </code></pre> <p>The following query is working fine (using just users,forum):</p> <pre><code>SELECT f1.match_static_id, f2.comments_no, f2.maxtimestamp, users.username FROM forum AS f1 INNER JOIN ( SELECT match_static_id, max(timestamp) maxtimestamp, count(match_static_id) AS comments_no FROM forum GROUP BY match_static_id ) AS f2 ON f1.match_static_id = f2.match_static_id AND f1.timestamp = f2.maxtimestamp INNER JOIN users on users.user_id = f1.user_id Order BY f2.maxtimestamp DESC </code></pre> <p>But when I try to query some data from the third table also with this query:</p> <pre><code>SELECT f1.match_static_id, f2.comments_no, f2.maxtimestamp, users.username, matches.team_name FROM forum AS f1 INNER JOIN ( SELECT match_static_id, max(timestamp) maxtimestamp, count(match_static_id) AS comments_no FROM forum GROUP BY match_static_id ) AS f2 ON f1.match_static_id = f2.match_static_id AND f1.timestamp = f2.maxtimestamp INNER JOIN users on users.id = f1.user_id INNER JOIN matches on matches.match_static_id = f2.match_static_id Order BY f2.maxtimestamp DESC </code></pre> <p>The result was duplicated (each record is duplicated) I do not why I see everything is ok so if anybody have any idea that could help me please HELP!!!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload