[SOLVED] Query problem

[Yuki]

 

SELECT count(student_answer.choice_No) , choice_No

FROM student_answer

LEFT JOIN choice

USING (choice_No)

WHERE student_answer.question_ID = "2" and choice.question_ID = "2"

GROUP BY choice.choice_No

ORDER BY choice.choice_No;

 

 

this returns me the choice an number of times it was chosen, how do I also include the MAX count of student_answer.choice no?

[Yuki]

I solved the above problem by running the same query twice and once with limit 1 and descending to get the most chosen.

 

 

However I have another problem, how do I get it to return choices that weren't chosen too?

[fenway]

You need to see for choice.question_ID IS NULL.

[gluck]

I solved the above problem by running the same query twice and once with limit 1 and descending to get the most chosen.

 

 

However I have another problem, how do I get it to return choices that weren't chosen too?

outer join on choice

[Yuki]

SELECT count(student_answer.choice_No) 'counted', choice_No 'no', choice.choice_Desc 'desc'

FROM student_answer

LEFT JOIN choice

USING (choice_No)

  WHERE student_answer.question_ID = '3' and choice.question_ID = '3' or choice.choice_No IS NULL

GROUP BY choice.choice_No

  ORDER BY choice.choice_No;

 

 

Is what I'm at so far with fenways suggestion.. it's not working however

[fenway]

You have to move the non-null part of the where clause on the on clause (which you don't even have) and then switch the OR to AND.

[Yuki]

You have to move the non-null part of the where clause on the on clause (which you don't even have) and then switch the OR to AND.

 

so I need to use ON instead of using?

 

 

to gluck:

 

SELECT count(student_answer.choice_No) 'counted', choice_No 'no', choice.choice_Desc 'desc'

FROM student_answer

LEFT OUTER JOIN choice

USING (choice_No)

   WHERE student_answer.question_ID = '2' and choice.question_ID = '2'

GROUP BY choice.choice_No

   ORDER BY choice.choice_No;

 

 

didn't work either

 

 

 

 

EDIT:

 

 

 

SELECT count(student_answer.choice_No) 'counted', choice.choice_No 'no', choice.choice_Desc 'desc'

FROM student_answer

LEFT JOIN choice

ON choice.choice_No IS NULL or choice.choice_No = student_answer.choice_No

  WHERE student_answer.question_ID = '2' and choice.question_ID = '2'

GROUP BY choice.choice_No

  ORDER BY choice.choice_No

 

 

tried this also

[fenway]

I mean this:

SELECT count(student_answer.choice_No) 'counted', choice.choice_No 'no', choice.choice_Desc 'desc'
FROM student_answer
LEFT JOIN choice
ON ( choice.choice_No = student_answer.choice_No AND choice.question_ID = '2')
   WHERE student_answer.question_ID = '2' and choice.choice_No IS NULL
GROUP BY choice.choice_No
   ORDER BY choice.choice_No

[Yuki]

just did some work and got

 

SELECT *

FROM student_answer

RIGHT OUTER JOIN choice

ON (student_answer.choice_No = choice.choice_No) and (student_answer.question_ID = choice.question_ID)

WHERE choice.question_ID = '2';

 

to work, it's not the full statement but I was just testing with outer join, i'll try your query now thanks

 

 

EDIT: fenway thanks for your help but I don't think your query is looking for what it should do, which is my fault because I edited out the database structure link by accident, but thank you.

 

 

I came to this conclusion:

 

 

SELECT count(student_answer.choice_No) 'counted', choice.choice_No 'no', choice.choice_Desc 'desc'

FROM student_answer

RIGHT OUTER JOIN choice

ON (student_answer.choice_No = choice.choice_No) and (student_answer.question_ID = choice.question_ID)

WHERE choice.question_ID = '2'

GROUP BY choice.choice_No

ORDER BY choice.choice_No;

 

 

that returns

 

 

 

counted  no  desc 

0 1 Indigo

1 2 Blue

0 3 Orange

0 4 internet0 5 asdad

0 6 dasdasd

2 7 asdadsdd

 

 

which is what I was after, thanks.