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 electionparticipants table would be a simple join table between
candidate and election.
Without those two tables, you're going to have some issues trying to do what
you want.
----- Original Message -----
From: "Larry Kim" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, March 05, 2001 3:54 AM
Subject: SQL query problem with mysql.
> 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,
> firstname varchar(32) not null,
> lastname varchar(32) not null,
> politicalparty varchar(32) not null,
> primary key(candidatenumber));
>
>
> create table votes (
> votenumber integer not null auto_increment,
> candidatenumber integer,
> countynumber integer,
> primary key(votenumber),
> foreign key(candidatenumber) references candidate,
> foreign key(countynumber) references county);
>
>
> i want to do a query that shows firstname, lastname, the number of votes
for
> that guy, and the total number of votes cast as illustrated:
>
> George, Bush, 2, 10
> Al, Gore, 2, 10
> Pat, Buchannan, 1, 10
> Ralph, Nader, 5, 10
>
> for example ralph nader received 5 votes out of a total of 10 cast.
> Al gore received 2 votes out of 10 ... you get the idea.
>
> here is my query:
>
> SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(Votes.VoteNumber) ,
> count(select * from Votes)
> FROM Candidate LEFT OUTER JOIN Votes ON Candidate.CANDIDATENUMBER =
> Votes.CANDIDATENUMBER
> GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME
>
> everything works except for the nasty count(select (*) from votes) which
> seems to work on other db's.
> if i take it away then it works fine. any ideas on how i could obtain a
> count of the number of votes
> cast?
>
>
> thank you for your consideration.
>
> Larry Kim
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
