List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:January 17 2009 9:24pm
Subject:Re: help refactoring query
View as plain text  
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
Thread
help refactoring queryb17 Jan
  • Re: help refactoring queryShawn Green17 Jan