Jeff Swanson
|
| Posted: 07/24/2002, 11:33 AM |
|
I'm trying to calculate the total points that a racer has accumulated for the year for a go-kart club. I am using several tables and can display fields from several tables without a problem. My problem exists when I add a SQL statement in order to do the calulating. When I do that, none of the fields will show values from the related tables. i.e. The driver names no longer display, only the "racer_id" field. Here is the sql code:
select racer_id, class_id, kart_no, rookie, sum(final_points) as sub_points from race_stats group by racer_id;
What I want to do is display the driver's name via the "driver" table. The driver table is joined to the "race_stat" table via the field "racer_id". I actually join several other tables, but if I could see how to get this one working, I'm sure I could then get the others. i.e. I want to display what class the racer is in, not the class_id etc.
|
|
|
 |
Jeff Swanson
|
| Posted: 07/24/2002, 12:23 PM |
|
I should mention that I am using MySQL and PHP.
I can even get it to do what I want from the mysql client (dos) with the following:
SELECT `classes`.`class_name`,
`drivers`.`name`,
`rookies`.`yes_no`,
`race_stats`.`racer_id`,
`race_stats`.`class_id`,
`race_stats`.`kart_no`,
`race_stats`.`rookie`,
SUM( `race_stats`.`final_points` ) AS sub_points
FROM `race_stats`
INNER JOIN `drivers` ON (`race_stats`.`racer_id` = `drivers`.`racer_id`)
INNER JOIN `classes` ON (`race_stats`.`class_id` = `classes`.`class_id`)
INNER JOIN `rookies` ON (`race_stats`.`rookie` = `rookies`.`rookie`)
GROUP BY `classes`.`class_name`, `drivers`.`name`, `rookies`.`yes_no`, `race_stats`.`racer_id`, `race_stats`.`class_id`, `race_stats`.`kart_no`, `race_stats`.`rookie`
ORDER BY `race_stats`.`class_id`, 8 DESC
However this does not work in CC.
Thanks
|
|
|
|
Jeff Swanson
|
| Posted: 07/24/2002, 12:47 PM |
|
Nevermind...got it working with the following in the SQL of the form
SELECT `classes`.`class_name`,
`race_stats`.`kart_no`,
`drivers`.`name`,
SUM( `race_stats`.`final_points` ) AS sub_points,
`rookies`.`yes_no`
FROM `race_stats`
INNER JOIN `drivers` ON (`race_stats`.`racer_id` = `drivers`.`racer_id`)
INNER JOIN `classes` ON (`race_stats`.`class_id` = `classes`.`class_id`)
INNER JOIN `rookies` ON (`race_stats`.`rookie` = `rookies`.`rookie`)
GROUP BY `classes`.`class_name`, `race_stats`.`kart_no`, `drivers`.`name`, `rookies`.`yes_no`
ORDER BY `classes`.`class_name`, 4 DESC
Not sure why the other didn't work. Oh, well, as long as it works.
|
|
|
|
|
