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
