join woes

I 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.

Re: join woes

On Mar 26, 10:16 am, "Dave" wrote:
> I 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.

At a guess:

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

Re: join woes

Thanks 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.

"strawberry" wrote in message
news:1174932427.549427.173870@e65g2000hsc.googlegroups.com.. .
> On Mar 26, 10:16 am, "Dave" wrote:
>> I 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.
>
> At a guess:
>
> 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
>

Re: join woes

On Mar 27, 10:58 am, "Dave" wrote:
> Thanks 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.
>
> "strawberry" wrote in message
>
> news:1174932427.549427.173870@e65g2000hsc.googlegroups.com.. .
>
> > On Mar 26, 10:16 am, "Dave" wrote:
> >> I 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.
>
> > At a guess:
>
> > 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

Well I was close:

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

Re: join woes

Unfortunately still no go!

"strawberry" wrote in message
news:1174998379.646528.307530@n76g2000hsh.googlegroups.com.. .
> On Mar 27, 10:58 am, "Dave" wrote:
>> Thanks 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.
>>
>> "strawberry" wrote in message
>>
>> news:1174932427.549427.173870@e65g2000hsc.googlegroups.com.. .
>>
>> > On Mar 26, 10:16 am, "Dave" wrote:
>> >> I 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.
>>
>> > At a guess:
>>
>> > 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
>
> Well I was close:
>
> 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
>

Re: join woes

On Mar 28, 12:52 pm, "Dave" wrote:
> Unfortunately still no go!
>
> "strawberry" wrote in message
>
> news:1174998379.646528.307530@n76g2000hsh.googlegroups.com.. .
>
> > On Mar 27, 10:58 am, "Dave" wrote:
> >> Thanks 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.
>
> >> "strawberry" wrote in message
>
> >>news:1174932427.549427.173870@e65g2000hsc.googlegroups.com ...
>
> >> > On Mar 26, 10:16 am, "Dave" wrote:
> >> >> I 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.
>
> >> > At a guess:
>
> >> > 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
>
> > Well I was close:
>
> > 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;

Re: join woes

Thanks for this, i've just tried it and the result I was expecting (and we
may have crossed wires here) would be John has 3, george has 1, ringo has 1,
and poor old Paul has 0, but Paul doesn't appear in the query result.

Is this possible?

Thanks for your time on this.

"strawberry" wrote in message
news:1175083905.577389.214240@p15g2000hsd.googlegroups.com.. .
> On Mar 28, 12:52 pm, "Dave" wrote:
>> Unfortunately still no go!
>>
>> "strawberry" wrote in message
>>
>> news:1174998379.646528.307530@n76g2000hsh.googlegroups.com.. .
>>
>> > On Mar 27, 10:58 am, "Dave" wrote:
>> >> Thanks 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.
>>
>> >> "strawberry" wrote in message
>>
>> >>news:1174932427.549427.173870@e65g2000hsc.googlegroups.com ...
>>
>> >> > On Mar 26, 10:16 am, "Dave" wrote:
>> >> >> I 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.
>>
>> >> > At a guess:
>>
>> >> > 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
>>
>> > Well I was close:
>>
>> > 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;
>

Re: join woes

On Mar 28, 1:39 pm, "Dave" wrote:
> Thanks for this, i've just tried it and the result I was expecting (and we
> may have crossed wires here) would be John has 3, george has 1, ringo has 1,
> and poor old Paul has 0, but Paul doesn't appear in the query result.
>
> Is this possible?
>
> Thanks for your time on this.
>
> "strawberry" wrote in message
>
> news:1175083905.577389.214240@p15g2000hsd.googlegroups.com.. .
>
> > On Mar 28, 12:52 pm, "Dave" wrote:
> >> Unfortunately still no go!
>
> >> "strawberry" wrote in message
>
> >>news:1174998379.646528.307530@n76g2000hsh.googlegroups.com ...
>
> >> > On Mar 27, 10:58 am, "Dave" wrote:
> >> >> Thanks 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.
>
> >> >> "strawberry" wrote in message
>
> >> >>news:1174932427.549427.173870@e65g2000hsc.googlegroups.com ...
>
> >> >> > On Mar 26, 10:16 am, "Dave" wrote:
> >> >> >> I 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.
>
> >> >> > At a guess:
>
> >> >> > 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
>
> >> > Well I was close:
>
> >> > 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;

Oh yeah, missed that. Also, I just realised that 'ignore' is probably
a reserved word. Enclosing it in back-ticks will sort that out.

Anyway, someone can probably think of a more efficient solution, but
here's a crude way:

SELECT a.nominee_id, a.nominee, IFNULL( b.total, 0 ) total
FROM (

SELECT `nominee_id` , nominees.`nominee`
FROM nominees
)a
LEFT JOIN (

SELECT nominees.`nominee_id` , 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
)b ON a.nominee_id = b.nominee_id;