MySQL Count() & Join problem

[silfreed]

Hi; I'll get right to my description and problem:
I am trying to design a poll. I am trying to build results for it by using two tables: poll_options and poll_votes. So, what I want to do is count how many times an option was voted for -and- include the option information. I've tried this, but I want the count() to return 0 (zero) also instead of just leaving that row out if there are no votes for an option. Here's my sql:

select poll_options., poll_votes., count(poll_votes.polloptionid) from poll_options, poll_votes where poll_options.polloptionid = poll_votes.polloptionid group by poll_votes.polloptionid;

If you need table definitions and data, let me know.

[[deleted]]

you should be able to get away with a LEFT JOIN between your options and your votes, and some fiddling in the select.
In a LEFT JOIN, both tables are merged according the a rule (ON bla=bla)
If the RIGHT (second) table cannot match the ON rule, NULLS are inserted instead.
If you do a count() of the results, the entries with the NULL stil count, so you get count()=1 even where no records exist.
Use an SUM and an IF to get the real count.

something like this (play with it in the mysql commandline mode):

select a.polloptionsid,
b.polloptionsid,
count(*),
sum(if(b.polloptionsid is NULL,0,1))
from poll_options as a
LEFT JOIN poll_votes as b
on poll_options.polloptionsid=poll_votes.polloptionsid
group by 1;

[silfreed]

Thanks a lot. That worked perfectly.. guess I need to do some reading on left joins..

[Anon]

Try "...from poll_options left join poll_votes...". Left join will return all rows from the left table (poll_options). If there's no matching record in the right table it will put NULLs in the right table fields. This could cause problems in counting (I don't remember whether mysql includes NULL fields in the count or not), but I'm sure there are ways to deal with that if it's an issue.

HTH,
Beth

[[deleted]]

I recommend you read all of the manual, or at least page through it reading just the topics. It is full of helpfull information that might give you new ideas for queries.

[Anon]

hey vincent. you are a savior! i've been racking my brains for a solutions exactly like this. gee thanks for this tip. i found it from one of the search engines.

hobbes

[[deleted]]

You're welcome :-)