poll display data

[balkan7]

Hi, i need help for get data from following tables:

 

questions:

id

question

 

options:

id

ques_id

value

 

votes:

id

option_id

voted_on

ip

 

Question: What is your favorite color.

Option1: Test 1  - 83 votes

Option2: Test 2 - 0 votes

Option3: Test 3 - 51 votes

 

I'm using this sql for result:

SELECT options.id, options.value, COUNT(*) as votes FROM votes, options WHERE votes.option_id=options.id AND votes.option_id IN(SELECT id FROM options WHERE ques_id=2) GROUP BY votes.option_id;

so i get this:

id   value   votes

1    Test1     83

3    Test3     51

 

because there is not votes for option Test2 and doesn't show it.

i need help to get all data like this:

 

id   value   votes

1    Test1     83

2    Test2      0

3    Test3     51

[Barand]

Use LEFT JOIN

SELECT options.id, options.value, COUNT(votes.id) as votes 
FROM options 
    LEFT JOIN votes ON votes.option_id=options.id
WHERE options.ques_id = 2
GROUP BY options.id