b wrote:
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.
Why not use just your subquery as your VIEW?
SELECT d.id, d.name, du.user_id as u_id,
FROM disciplines AS d
LEFT JOIN disciplines_users AS du
ON d.id = du.discipline_id
GROUP BY d.id, d.name
The reason I inverted the FROM and LEFT JOIN was so that if you had a
discipline with 0 users, you can now see a zero. In your original
orientation, a relationship had to exist or its discipline wouldn't have
been counted.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org