View Single Post
Old 09-18-2012, 11:02 PM   PM User | #2
Old Pedant
Supreme Master coder!

 
Old Pedant's Avatar
 
Join Date: Feb 2009
Posts: 23,542
Thanks: 62
Thanked 4,054 Times in 4,023 Posts
Old Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to all
I *think* this does it:
Code:

SELECT team.TeamID, team.teamName, allresults.wintotal, allresults.individualWins
FROM team
LEFT JOIN ( 
     ( SELECT rTeamID, SUM( IF(wld='w', 2, IF(wld='d', 1, 0 ) ) ) AS wintotal
       FROM results
       GROUP BY rTeamID ) AS tr
     INNER JOIN 
     ( SELECT x.iTeamID, COUNT(*) AS individualWins
       FROM (
              SELECT iTeamID FROM individResults WHERE fa='f'
              UNION ALL 
              SELECT iTeamID FROM individResultsDoubles WHERE fa='f'
            ) AS x
       GROUP BY x.iTeamID
     ) AS ir
     ON tr.rTeamID = ir.iTeamID
   ) AS allresults
ON team.teamID = allresults.rTeamID
ORDER BY allresults.wintotal, allresults.individualWins
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pedant is offline   Reply With Quote