|
Posted by Dave on March 28, 2007, 7:52 am
Please log in for more thread options Unfortunately still no go!
>> 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.
>>
>>
>>
>> >> 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
>
| 
XML Sitemap