One Query to find result from multiple tables

[crashwave]

I had to create a 3NF model and Database from a single spreadsheet. Here is what I came up with

http://picasaweb.google.com/lh/photo/jOrl3AZM0wWGLVK4roxUaCg4DBDpe4IEPAbqDkhUXMw database has been created and all data inserted.

Description

Clubs enter tournament. Each club has 5 players with a rank to play same ranked players from other clubs.

Each player plays match(es) in each tournament and gets a score (0,1,2 points)

There are many tournaments each with many matches.

What I need is 1 query to find the club with the most points in a tournament.

I have the logic I think

Get total points for each player in a tournament. Group them by club. find sum of all club players.

So I need the output to be

+-------+---------+

|CLUB  |POINTS |

+-------+---------+

| Club1 |  10    |

| Club2 |  20    |

| Club3 |  30    |

 

Or just the highest club. But nothing I do works.

Any ideas please?

also any input on model/DB appreciated.

Thanks

 

 

[mikosiko]

explain the purpose of your point_list table please... and test this  (no tested in my side... the SUM(c.POINT_RESULT) could be incorrect depending on the meaning of your table point_list)

 

SELECT b.Club_ID,
       SUM(c.POINT_RESULT) AS TPoints
  FROM match2players a
   JOIN player2club b ON a.P2C_ID = b.p2c_id
   JOIN point_list c ON a.points = c.points
   JOIN match2tournamet d ON a.m2t_id = d.m2t_id
   WHERE d.tourn_id = $tournamentid
   GROUP by b.Club_ID 
   ORDER BY TPoints DESC 

[crashwave]

I put the image here http://picasaweb.google.com/lh/photo/s6b82nLc84H5awZhcy-GIig4DBDpe4IEPAbqDkhUXMw

for reference deleting it later this week.

The point_list just has point 0,1,2 and what they mean Loss,Draw,Win

Thanks I did something similar without JOINS (they confuse me) Need to try with Joins

 

I Used

SELECT        tournaments.TOURN_ID, clubs.Club_Name, SUM(match2player.POINTS) AS Tourn_Points

FROM            match2player, match2tournament, player2club, tournaments, clubs

WHERE        match2player.M2T_ID = match2tournament.M2T_ID AND match2player.P2C_ID = player2club.p2c_ID AND

                        match2tournament.TOURN_ID = tournaments.TOURN_ID AND player2club.Club_ID = clubs.club_id

GROUP BY tournaments.TOURN_ID, clubs.club_id

 

It displayed the Tournament_ID | Club Name | Tourn_Points

 

more tweaking and I can get just the highest. or just tourn by user input.

 

[mikosiko]

....

Thanks I did something similar without JOINS (they confuse me) Need to try with Joins

for your knowledge.....  you ARE using JOINS.... only difference is you are using "Implicit JOINS"

 

....

WHERE match2player.M2T_ID = match2tournament.M2T_ID

    AND match2player.P2C_ID = player2club.p2c_ID

    AND match2tournament.TOURN_ID = tournaments.TOURN_ID

    AND player2club.Club_ID = clubs.club_id

....

 

 

all those xxx = yyy ARE implicit joins

[crashwave]

I actually like yours a lot better, little tweaking here it is

SELECT c.Club_name, SUM( m.POINTs ) AS TPoints, t.tourn_id
FROM match2player m
JOIN player2club p ON m.P2C_ID = p.p2c_id
JOIN clubs c ON p.club_id = c.club_id
JOIN match2tournament d ON m.m2t_id = d.m2t_id
JOIN tournaments t ON d.tourn_id = t.tourn_id
WHERE t.tourn_id = $tournamentid
GROUP BY c.Club_ID
ORDER BY TPoints DESC