Home » SQL & PL/SQL » SQL & PL/SQL » Whats wrong with these joins? Please help !!!!
Whats wrong with these joins? Please help !!!! [message #225348] Mon, 19 March 2007 13:52 Go to next message
auhasan
Messages: 1
Registered: March 2007
Junior Member
I have 2 tables

EXAM_QUESTIONS: question_Id, language_id, category_id, question_text, is_active
EXAM_RESPONSES: question_Id, chosen_answer, is_correct

and I am trying to get report in following columns

QUESTION ID | TIMES ASKED | TIMES CORRECT | CORRECT % | OPT A CHOSEN | OPT B CHOSEN

and I am trying to use following query without luck (although query runs but numbers are not summing up!)

select eq.question_id,
count(er1.QUESTION_ID) as times_asked,
count(er2.QUESTION_ID) as times_correct,
count(er3.QUESTION_ID) as Answer_A_Selected,
count(er4.QUESTION_ID) as Answer_B_Selected,
eq.language_Id,
eq.category_Id
from exam_question eq left join EXAM_RESPONSE er1 on (eq.QUESTION_ID = er1.QUESTION_ID)
left join EXAM_RESPONSE er2 on (eq.QUESTION_ID = er2.QUESTION_ID AND er2.IS_CORRECT = 'T')
left join EXAM_RESPONSE er3 on (eq.QUESTION_ID = er3.QUESTION_ID AND er3.CHOSEN_ANSWER = 'A')
left join EXAM_RESPONSE er4 on (eq.QUESTION_ID = er4.QUESTION_ID AND er4.CHOSEN_ANSWER = 'B')
WHERE eq.IS_ACTIVE = 'T'
group by eq.QUESTION_ID, eq.CATEGORY_ID, eq.LANGUAGE_ID
order by eq.language_id, eq.category_id


someone please help. Am i joining tables correctly?

Thanks in advance
Re: Whats wrong with these joins? Please help !!!! [message #225368 is a reply to message #225348] Mon, 19 March 2007 15:08 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Your joins are fine. Your query is just wrong. I think this is what you want.
SELECT eq.question_id
      ,COUNT(er.question_id) AS times_asked
      ,COUNT(CASE is_correct WHEN 'T' THEN 1 END) AS times_correct
      ,COUNT(CASE chosen_answer WHEN 'A' THEN 1 END) AS answer_a_selected
      ,COUNT(CASE chosen_answer WHEN 'B' THEN 1 END) AS answer_b_selected
      ,eq.language_id
      ,eq.category_id
FROM exam_question eq
LEFT JOIN exam_response er ON eq.question_id = er.question_id
WHERE eq.is_active = 'T'
GROUP BY eq.question_id, eq.category_id, eq.language_id
ORDER BY eq.language_id, eq.category_id
Previous Topic: intersection
Next Topic: Day number in a Quarter
Goto Forum:
  


Current Time: Mon Dec 05 07:15:27 CST 2016

Total time taken to generate the page: 0.10079 seconds