I have a query involving three tables. The relevant fields are listed here: users(id, role_id, geo_id, first_name, last_name), cities(city_name), and a junction table coordinators_clients(coordinator_id, client_id) where there is a many-to-many relationship. A user who is a coordinator will have role_id = 2.
I would like to list all the coordinators with their cities, and the number of clients that they have.
So far, my query is:
SELECT users.id,
CONCAT(' ', users.first_name, users.last_name) AS 'Coordinator',
cities.city_name AS 'City'
FROM users
JOIN cities ON cities.id = users.geo_id
WHERE users.role_id = 2
ORDER BY cities.city_name ASC, users.last_name ASC, users.first_name ASC;
I am having problems adding a column showing the total number of clients that each coordinator has. I have this -
COUNT(coordinators_clients.client_id) AS 'Number of Clients'
GROUP BY coordinators_clients.coordinator_id
How do I integrate that into the main query? Everything I have tried so far has either resulted in an error, or a table with duplicate entries of coordinators.
looks like a single coordinator may have multiple cities as well as multiple clients
gotta put one of those in a subquery
easy to make the choice which one it should be, since you’re elaborating the cities but counting the clients
SELECT cities.city_name AS 'City'
, users.id
, CONCAT(' ', users.first_name, users.last_name) AS 'Coordinator'
, sub.nmbr_clnts AS 'Number of Clients'
FROM users
INNER
JOIN cities
ON cities.id = users.geo_id
INNER
JOIN ( SELECT coordinator_id
, COUNT(*) AS nmbr_clnts
FROM coordinators_clients
GROUP
BY coordinator_id ) AS sub
ON sub.coordinator_id = users.id
WHERE users.role_id = 2
ORDER
BY cities.city_name ASC
, users.last_name ASC
, users.first_name ASC
There is a many-to-one relationship between coordinator and city. So a coordinator will only have one city.
As for CONCAT_WS, I had never heard of that, but it does seem to be the one to use. Thank you.
If I wanted to also include all the coordinators who didn’t have any clients, I would change the INNER JOIN to a LEFT JOIN, right? (BTW, all coordinators are associated with a city).
SELECT cities.city_name AS 'City'
, users.id
, CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
, COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients'
FROM users
INNER
JOIN cities
ON cities.id = users.geo_id
LEFT OUTER
JOIN coordinators_clients
ON coordinators_clients.coordinator_id = users.id
WHERE users.role_id = 2
ORDER
BY cities.city_name ASC
, users.last_name ASC
, users.first_name ASC
SELECT cities.city_name AS 'City'
, users.id
, CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
, COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients'
FROM users
INNER
JOIN cities
ON cities.id = users.geo_id
LEFT OUTER
JOIN coordinators_clients
ON coordinators_clients.coordinator_id = users.id
WHERE users.role_id = 2
GROUP
BY cities.city_name
, users.id
ORDER
BY cities.city_name ASC
, users.last_name ASC
, users.first_name ASC
Okay, I now need to add a complication to this - I need to filter this by the created_at date in the clients table. So I will only count the number of clients who registered between a given $start_date and $end_date.
Would I just get all the clients with client_id from the coordinators_clients table, and filter them by the created_at date and use the above query on the results? How would I do that?
SELECT cities.city_name AS 'City'
, users.id
, CONCAT_WS(' ', users.first_name, users.last_name) AS 'Coordinator'
, COUNT(coordinators_clients.coordinator_id) AS 'Number of Clients'
FROM users
INNER
JOIN cities
ON cities.id = users.geo_id
LEFT OUTER
JOIN coordinators_clients
ON coordinators_clients.coordinator_id = users.id
AND coordinators_clients.client_id IN
(SELECT id
FROM clients
WHERE created_at BETWEEN '$start_date' AND '$end_date')
WHERE users.role_id = 2
GROUP
BY cities.city_name
, users.id
ORDER
BY cities.city_name ASC
, users.last_name ASC
, users.first_name ASC