MySQL Query Returns Different Results

Question

I'm having a strange problem with the following query:

SELECT
    doubles_team_members.doubles_team_id,
    GROUP_CONCAT(CONCAT(members.first_name,' ',members.last_name) SEPARATOR ' & ') team,
    GROUP_CONCAT(DISTINCT CONCAT(members.city,', ',members.state) SEPARATOR ' & ') location,
    doubles_teams.deleted_by_id
FROM
    doubles_team_members
JOIN
    doubles_teams USING(doubles_team_id)
JOIN
    members USING(member_id)
JOIN
    memberships ON members.member_id=memberships.member_id
WHERE
    (memberships.expiration_date>=CURDATE() OR members.lifetime=1) AND 
    (doubles_teams.deleted_by_id IS NULL OR doubles_teams.deleted_by_id=0) AND 
    (members.deleted_by_id IS NULL OR members.deleted_by_id=0)
GROUP BY
    doubles_team_members.doubles_team_id
HAVING
    team='Van Williams & Derek Williams';

Sometimes I get zero, one, two or three results. I should always get three results. What could be causing this?

Answer 1

As pointed out by Solarflare, GROUP_CONCAT() could produce your team string in a random order, e.g. 'Van Williams & Derek Williams' or 'Derek Williams & Van Williams'.

To remedy this, you can use an explicit ORDER BY within the GROUP_CONCAT(), e.g.:

GROUP_CONCAT(CONCAT(members.first_name,' ',members.last_name) ORDER BY first_name DESC SEPARATOR ' & ') team

See documentation and this db<>fiddle for an example.