I am trying to develop a database structure for my local bowsl clubs
which has a members table and a number of tables scoring information
on competition ranking points. All works fine if each of the points
tables has a value for the selected id
My query is
SELECT m.id, m.fname, m.sname, m.club,
SUM(a.2ws + a.4ws + a.2wp + a.4wp + a.mp + a.ct + a.cf + a.mf + a.To3
+ a.CoC + a.U25) AS '0304',
SUM(b.2ws + b.4ws + b.2wp + b.4wp + b.mp + b.ct + b.cf + b.mf + b.To3
+ b.CoC + b.U25) AS '0405',
SUM(c.2ws + c.4ws + c.2wp + c.4wp + c.mp + c.ct + c.cf + c.mf + c.To3
+ c.CoC + c.U25) AS '0506',
SUM(a.2ws + a.4ws + a.2wp + a.4wp + a.mp + a.ct + a.cf + a.mf + a.To3
+ a.CoC + a.U25 + b.2ws + b.4ws + b.2wp + b.4wp + b.mp + b.ct + b.cf +
b.mf + b.To3 + b.CoC + b.U25 + c.2ws + c.4ws + c.2wp + c.4wp + c.mp +
c.ct + c.cf + c.mf + c.To3 + c.CoC + c.U25) AS 'total'
FROM members m
LEFT OUTER JOIN points0304 a
ON m.id = a.id
LEFT OUTER JOIN points0405 b
ON m.id = b.id
LEFT OUTER JOIN points0506 c
ON m.id = c.id
GROUP BY m.id
ORDER BY total DESC LIMIT 0, 30
The output is:
id fname sname club 0304 0405 0506 total
427 Percy Tripp Helston 20 33 2 55
575 Joan Prout Cubert 2 0
1001 Una Prideaux Troon 5
562 NicholasTruscott Perranporth 4 10
The gaps in out put are fine e.g for id 575 there is no value in table
0304. What I do not uderstad is why the total does not work.
Any Ideas?
------------------------ Yahoo! Groups Sponsor --------------------~-->
Most low income households are not online. Help bridge the digital divide today!
http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/CefplB/TM
--------------------------------------------------------------------~->
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
