I'm having some difficulty getting my head around a particular query.
I'd like to make this a view once I get something working. However, all
I've been able to come up with uses a sub-query. So, no view on the horizon.
I have 3 tables:
users
id,
(etc. the usual)
disciplines
id,
name (ie. film, photography, writing, etc.)
disciplines_users
discipline_id,
user_id
Each user may have one or more discipline.
The view I'm looking for shows the total number of users who have a
particular discipline. NOTE: the sum of the totals is greater than the
total number of users, which is by design.
SELECT name, COUNT(discipline.u_id) AS total
FROM (
SELECT du.discipline_id, du.user_id as u_id, d.name
FROM disciplines_users AS du
LEFT JOIN disciplines AS d
ON d.id = du.discipline_id
) AS discipline
GROUP BY discipline.name ORDER BY discipline.name;
+---------------------+-------+
| name | total |
+---------------------+-------+
| Dance | 176 |
| Film and Television | 376 |
etc.
I've a feeling that this could be done without that sub-query and using
another join. If not, I might make the sub-query its own view and see
what the performance is like. I'd appreciate any suggestions, especially
any pointers on refactoring sub-queries into joins, in general.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
