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 lkim@xxxxxxxxxxxxxxx larry122@xxxxxxxxx
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 <mysql-thread67424@xxxxxxxxxxxxxxx> To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php