>hi,
>
>i'm writing a book (wrox publishers) which uses mysql for the sample
>database.
>i seem to have encountered a problem with an SQL query.
>its a simple voting application, with a candidate table, and a vote table:
>
>create table candidate (
>candidatenumber integer not null auto_increment,
I think you really need another couple tables, even though your just writing
a sample.
You probably need a table named 'election' and one named
'electionparticipants'
Your election table would contain things like the date of the election, the
total number of votes cast, etc.
The electionparticip
I would make the select statement look like this
SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(Votes.VoteNumber) ,
count(select votenumber from Votes)
FROM Candidate LEFT OUTER JOIN Votes ON Candidate.CANDIDATENUMBER =
Votes.CANDIDATENUMBER
GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME
