Try
SELECT
pd.ANSWER_TEXT, pr.*, COUNT(*) as NUM
FROM
pollresponse left join polldata on pd.QUESTION_ID = pr.QUESTION_ID
WHERE
pd.VOTE_ID = pr.RESPONSE
GROUP BY pr.RESPONSE
Something like that will work better...probably you have to massage it a
little. Note that you were not getting any results because you had a full
join when you should have had a LEFT join. (that's a hint to the above).
There has to be a record in both tables to get results. You can read about
it here:
http://www.mysql.com/doc/J/O/JOIN.html
You might also consider the following data model
POLL
POLL_ID
POLL_description
ANSWER
POLL_ID
ANSWER_ID
ANSWER_description
RESPONSE
RESPONSE_ID
USER_ID (if you are tracking users)
POLL_ID
ANSWER_ID
Seems like that is normalized. You'll find the indexing more efficient.
-----Original Message-----
From: Chad Day [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 13, 2002 3:35 PM
To: mysql
Subject: Problems with a join...
I'm trying to write a polling application and display the results on an
admin screen.. my goal is to show all the answers for the poll questions and
display the # of votes for each. The query I have to do this is:
select pd.ANSWER_TEXT, pr.*, COUNT(*) as NUM from ft_pollresponses as pr
right join ft_polldata as pd on pd.QUESTION_ID = pr.QUESTION_ID and
pd.VOTE_ID = pr.RESPONSE GROUP BY pr.RESPONSE
but the problem is that if an answer has 0 votes, it doesn't show up in the
query results.. I know I'm doing something wrong with the join, but an
unsure what.. can anyone help? Below are my 2 table schemas.
CREATE TABLE ft_pollresponses (
POLL_ID int(11) DEFAULT '0' NOT NULL,
QUESTION_ID int(11) DEFAULT '0' NOT NULL,
RESPONSE varchar(200) NOT NULL,
KEY POLL_ID (POLL_ID),
KEY QUESTION_ID (QUESTION_ID)
);
CREATE TABLE ft_polldata (
POLL_ID int(11) DEFAULT '0' NOT NULL,
QUESTION_ID int(11) DEFAULT '0' NOT NULL,
ANSWER_TEXT char(175) NOT NULL,
VOTE_ID int(11) DEFAULT '0' NOT NULL auto_increment,
PRIMARY KEY (VOTE_ID)
);
Any help is much appreciated... Thanks!
Chad
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
