In my application, I have agents that are associated with multiple of locations, so my database is as follows (in bold are my table names, followed by field names):
So my last table is a linked table.
For each location, I need to retrieve a comma-separated list of agent ids associated with that location, so I am using the following code:
SELECT locs.id, name, default_time, default_vehicle, GROUP_CONCAT(DISTINCT assoc.agent_id) AS agent_ids
FROM locations AS locs
INNER JOIN location_agent_assoc AS assoc ON locs.id = assoc.location_id
INNER JOIN agents ON assoc.agent_id = agents.id
WHERE locs.archived = 0 AND agents.archived = 0
GROUP BY locs.id, name, default_time, default_vehicle
ORDER BY name, agents.fName, agents.lName
As you can see I am trying to order first by location name (works), then I would like to order the agent id list by first name, last name, but this does not give me the correct order of ids. What am I doing wrong?