Post by DaveUnfortunately still no go!
Post by strawberryPost by DaveThanks for the reply, but couldn't get this to work, with no votes present
there were no records from the query, and I was hoping to get all of the
nominees with '0' votes against each name.
Post by strawberryPost by DaveI have a database with 3 tables, one for nominees, one for voters and one
for votes.
I used the following syntax
SELECT
`tblnominees`.`fldNominee`,
Count(`tblvotes`.`fldVote`) AS `COUNT_OF_VOTES`
FROM
`tblnominees`
Left Join `tblvotes` ON `tblvotes`.`fldVote` =
`tblnominees`.`nomineeID`
GROUP BY
`tblnominees`.`fldNominee`
ORDER BY
`COUNT_OF_VOTES` DESC
and this worked well to give a list of all candidates, whether they had
votes or not (it returned '0' for those with no votes, which was perfect).
But now I have a need to use the voters table as well so I can use an ignore
field to exclude voters we don't want to count for whatever reason. I can't
work out how to do the joins so i still get the full list of nominees with
their vote count, whether thay have votes or not.
Could anyone shed some light please?
Appreciate the help.
SELECT nominees.nominee,count(votes.vote) as total FROM voters
LEFT JOIN votes ON votes.voter_id = voters.voter_id
LEFT JOIN nominees ON nominees.nominee_id = votes.nominee_id
WHERE voters.ignore = 0
GROUP BY votes.nominee
ORDER BY total DESC,nominees.nominee
SELECT nominees.nominee, count( votes.nominee_id ) AS total
FROM voters
LEFT JOIN votes ON votes.voter_id = voters.voter_id
LEFT JOIN nominees ON nominees.nominee_id = votes.nominee_id
WHERE voters.ignore =0
GROUP BY votes.nominee_id
ORDER BY total DESC , nominees.nominee
Given a schema like the one below, the query provided should work fine
(although you might have to retype the
line beginning 'WHERE...'. My machine doesn't like it when I copy and
paste that line for some reason:
CREATE TABLE `nominees` (
`nominee_id` int(11) NOT NULL auto_increment,
`nominee` varchar(25) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`nominee_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=5 ;
INSERT INTO `nominees` VALUES (1, 'John');
INSERT INTO `nominees` VALUES (2, 'Paul');
INSERT INTO `nominees` VALUES (3, 'George');
INSERT INTO `nominees` VALUES (4, 'Ringo');
CREATE TABLE `voters` (
`voter_id` int(11) NOT NULL auto_increment,
`ignore` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`voter_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=8 ;
INSERT INTO `voters` VALUES (1, 0);
INSERT INTO `voters` VALUES (2, 0);
INSERT INTO `voters` VALUES (3, 0);
INSERT INTO `voters` VALUES (4, 1);
INSERT INTO `voters` VALUES (5, 1);
INSERT INTO `voters` VALUES (6, 0);
INSERT INTO `voters` VALUES (7, 0);
CREATE TABLE `votes` (
`voter_id` int(11) NOT NULL,
`nominee_id` int(11) NOT NULL,
PRIMARY KEY (`voter_id`,`nominee_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `votes` VALUES (1, 1);
INSERT INTO `votes` VALUES (2, 1);
INSERT INTO `votes` VALUES (3, 1);
INSERT INTO `votes` VALUES (4, 2);
INSERT INTO `votes` VALUES (5, 2);
INSERT INTO `votes` VALUES (6, 3);
INSERT INTO `votes` VALUES (7, 4);
That query again:
SELECT nominees.nominee, count( votes.nominee_id ) AS total
FROM voters
LEFT JOIN votes ON votes.voter_id = voters.voter_id
LEFT JOIN nominees ON nominees.nominee_id = votes.nominee_id
WHERE voters.ignore =0
GROUP BY votes.nominee_id
ORDER BY total DESC , nominees.nominee;