1

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?

buddemat
  • 4,552
  • 14
  • 29
  • 49
Floyd Resler
  • 1,786
  • 3
  • 22
  • 41
  • 1
    Can you add some data please? Also, what exactly do you mean by "sometimes"? Like, when you run the query twice right after another or rather one day it's two and another day it's three records in the results? – buddemat Oct 07 '21 at 19:04
  • 1
    Something to consider: your group_concat has no inherent order. If MySQL randomly orders your members differently (e.g. derek first), the having clause may not match. – Solarflare Oct 07 '21 at 21:38

1 Answers1

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.

buddemat
  • 4,552
  • 14
  • 29
  • 49