EXPERT RESPONSE
The normalization is fine. The SCORES table does not need its own
surrogate (autonumber) key; the primary key should be the
composite of date_record_id, player_record_id, division_record_id,
and of course each of those is a foreign key back to its respective table.
Do not use the reserved word DATE to name the date field; choose something
like tournament_date.
The query to assign points based on your system is:
select p.player_name
, ( select count(*)
from scores
where player_record_id = p.record_id
) * 1 -- tournaments
+ ( select count(*)
from scores s
where player_record_id = p.record_id
and score =
( select min(score)
from scores
where division_record_id
= s.division_record_id )
) * 4 -- divisionwins
+ ( select count(*)
from scores s
inner
join scores s2
on s.division_record_id = s2.division_record_id
and s.player_record_id <> s2.player_record_id
and s.score < s2.score
where s.player_record_id = p.record_id
) * 2 -- finishaheadof
as totalpoints
from players p
order
by 2 desc
You mentioned you wanted no one to write this query for you, but sometimes
seeing the result is more helpful than reading a long explanation.
The query uses scalar subselects. Each subselect
selects rows to look at based on matching the value of the player_id
from the players table in the main query. In the main query,
the players table has as alias, p, which you can see being
used inside each subselect, in what looks like a join condition.
They are called correlated subselects because of this matching.
It almost looks like each subselect is "executed" over and over, once
for each row of the main players table. Perhaps there is
some appeal in imagining correlated subselects to work that way,
but it is a poor analogy, because the database optimizer
almost always works more efficiently than that.
The important point, though, is that each subselect returns a single value
(hence the name scalar) for each row of the main query.
As for your question about listing data by division, and only showing the
division once, like a heading, my advice is to avoid completely even thinking
about attempting to achieve this with SQL. Do report formatting in your
application program.
For More Information
|
