>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]
Sir, MySQL doesn't (yet) support subqueries. Generally, you get
around this by using TEMPORARY tables. The following, in slightly
different form, ran successfully on the MS Titanic (aka my Wintel
box).
CREATE TEMPORARY TABLE counts
SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(*)
FROM Candidate LEFT JOIN Votes
ON Candidate.CANDIDATENUMBER = Votes.CANDIDATENUMBER
GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME;
CREATE TEMPORARY TABLE total_votes
SELECT Count(*) AS total_votes FROM votes;
SELECT * FROM counts, total_votes;
You can also use
INSERT INTO counts
SELECT "Total", Count(*) FROM votes;
SELECT * FROM counts;
in place of the last two statements, which will give you the same
data in a different format. Your readers may find the second solution
easier to understand. The TEMPORARY tables are automatically removed
when the connection ends.
Note that the FOREIGN KEY clause in your CREATE TABLE statement has
no effect in MySQL. It is only there for compatibility with other
RDBMSs. In order to maximize speed, MySQL provides no relational
integrity constraints other than those applied to primary keys.
Foreign keys are not enforced.
It is possible that before your book goes to the printer, MySQL will
have added subqueries, and one developer or another will have created
code that makes it possible to add table types that support integrity
constraints. These things are all in the works from various sources.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
