Subquery or join help needed
Posted: Sun Aug 24, 2008 3:07 am
I have a set of tables: climbs, routes and areas. I'm having trouble getting the correct query for a summery. Maybe it's best to show what I have so far and what end-result I want.
which results in something like:
Now what I would like is to group by the grades, get the total number per grade but also get the number of climbs in each style per grade:
Hope it's clear what I want.
I have looked at using subqueries but so far without success. I could of course take the result set I have (shown above) and write some PHP loop with some logic to get the result I want, but if it's possible to do in in MySQL I'd prefer that.
Any ideas?
Code: Select all
$sql = " SELECT r.`route_grade`, c.`climb_style`
FROM `mc_routes` r, `mc_routeclimbs` c, `mc_areas` a
WHERE c.`route_id` = r.`route_id`
AND r.`area_id` = a.`area_id`
AND c.`user_id` = :id
ORDER BY r.`route_grade` DESC
";
Code: Select all
route_grade | climb_style
7c RP
7b+ RP
7b+ FL
7b+ RP
7b+ RP
7b RP
7b RP
7b RP
7a+ OS
7a+ RP
7a+ OS
7a+ RP
7a+ OS
7a OS
7a OS
7a RP
7a FL
6c+ OS
6c+ OS
6c OS
6b+ OS
6b+ OS
6b+ OS
6b+ OS
6b FL
6b OS
6b OS
6b OS
6b OS
6b OS
6a+ OS
6a+ OS
6a+ OS
6a OS
6a OS
6a OS
6a OS
5c OS
5c OS
Code: Select all
Grade | Total | OS | RP | FL
7c | 1 | 0 | 1 | 0
7b+ | 4 | 0 | 3 | 1
7b | 3 | 0 | 3 | 0
... etc
I have looked at using subqueries but so far without success. I could of course take the result set I have (shown above) and write some PHP loop with some logic to get the result I want, but if it's possible to do in in MySQL I'd prefer that.
Any ideas?