How to count number of different items in SQL

Question

Database structure:

Clubs: ID, ClubName
Teams: ID, TeamName, ClubID
Players: ID, Name
Registrations: PlayerID, TeamID, Start_date, End_date, SeasonID

Clubs own several teams. Players may get registered into several teams (inside same club or into different club) during one year. I have to generate a query to list all players that have been registered into DIFFERENT CLUBS during one season. So if player swapped teams that were owned by the same club then it doesn't count.

My attempts so far:

SELECT
c.short_name,
p.surname,
r.start_date,
r.end_date,
(select count(r2.id) from ejl_registration as r2
    where r2.player_id=r.player_id and r2.season=r.season) as counter
FROM
ejl_registration AS r
left Join ejl_players AS p ON p.id = r.player_id
left Join ejl_teams AS t ON r.team_id = t.id
left Join ejl_clubs AS c ON t.club_id = c.id
WHERE
r.season =  '2008'
having counter >1

I can't figure out how to count and show only different clubs... (It's getting too late for clear thinking). I use MySQL.

Report should be like: Player name, Club name, Start_date, End_date

Answer 1

Here's a list of players for one season.

SELECT sub.PlayerId
FROM
(
  SELECT
    r.PlayerId,
    (SELECT t.ClubID FROM Teams t WHERE r.TeamID = t.ID) as ClubID
  FROM Registrations r
  WHERE r.Season = '2008'
) as sub
GROUP BY PlayerId
HAVING COUNT(DISTINCT sub.ClubID) > 1

Here's a list of players and seasons, for all seasons.

SELECT PlayerId, Season
FROM
(
SELECT
  r.PlayerId,
  r.Season,
  (SELECT t.ClubID FROM Teams t WHERE r.TeamID = t.ID) as ClubID
FROM Registrations r
) as sub
GROUP BY PlayerId, Season
HAVING COUNT(DISTINCT sub.ClubID) > 1

By the way, this works in MS SQL.

Answer 2

This is a second try at this answer, simplifying it to merely count the distinct clubs, not report a list of club names.

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;

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.

There's no reason to use a LEFT JOIN as in your example.

---

Okay, here's the first version of the query:

You have a chain of relationships like the following:

club1 <-- team1 <-- reg1 --> player <-- reg2 --> team2 --> club2

Such that club1 must not be the same as club2.

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;

Answer 3

SELECT p.Name, x.PlayerID, x.SeasonID
  FROM (SELECT DISTINCT r.PlayerID, r.SeasonID, t.ClubID
          FROM Registrations r
          JOIN Teams t ON t.ID = r.TeamID) x
  JOIN Players p ON p.ID = x.PlayerID
 GROUP BY p.rName, x.PlayerID, x.SeasonID
HAVING COUNT(*) > 1