StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a second try at this answer, simplifying it to merely count the distinct clubs, not report a list of club names.</p> <pre><code>SELECT p.surname, r.start_date, r.end_date, COUNT(DISTINCT c.id) AS counter FROM ejl_players p JOIN ejl_registration r ON (r.player_id = p.id) JOIN ejl_teams t ON (r.team_id = t.id) JOIN ejl_clubs c ON (t.club_id = c.id) WHERE r.season = '2008' GROUP BY p.id HAVING counter > 1; </code></pre> <p>Note that since you're using MySQL, you can be pretty flexible with respect to columns in the select-list not matching columns in the GROUP BY clause. Other brands of RDBMS are more strict about the Single-Value Rule.</p> <p>There's no reason to use a LEFT JOIN as in your example.</p> <hr> <p>Okay, here's the first version of the query:</p> <p>You have a chain of relationships like the following:</p> <pre><code>club1 <-- team1 <-- reg1 --> player <-- reg2 --> team2 --> club2 </code></pre> <p>Such that club1 must not be the same as club2.</p> <pre><code>SELECT p.surname, CONCAT_WS(',', GROUP_CONCAT(DISTINCT t1.team_name), GROUP_CONCAT(DISTINCT t2.team_name)) AS teams, CONCAT_WS(',', GROUP_CONCAT(DISTINCT c1.short_name), GROUP_CONCAT(DISTINCT c2.short_name)) AS clubs FROM ejl_players p -- Find a club where this player is registered JOIN ejl_registration r1 ON (r1.player_id = p.id) JOIN ejl_teams t1 ON (r1.team_id = t1.id) JOIN ejl_clubs c1 ON (t1.club_id = c1.id) -- Now find another club where this player is registered in the same season JOIN ejl_registration r2 ON (r2.player_id = p.id AND r1.season = r2.season) JOIN ejl_teams t2 ON (r2.team_id = t2.id) JOIN ejl_clubs c2 ON (t2.club_id = c2.id) -- But the two clubs must not be the same (use < to prevent duplicates) WHERE c1.id < c2.id GROUP BY p.id; </code></pre>

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