Possible to avoid CREATE TABLE? 1

[wanttoscript]

I conduct the following 2 queries to get my answer set:

CREATE TEMPORARY TABLE temp_table
SELECT call_info.call_id, answers.answer
FROM answers INNER JOIN call_info ON answers.call_id = call_info.call_id
WHERE answers.question=1;

SELECT COUNT(temp_table.call_id) as cid, temptable.answer as A, answers.answer as B
FROM answers INNER JOIN temptable ON answers.call_id = temptable.call_id
WHERE answers.question=2
GROUP BY B, A
ORDER BY B, A;

Explaining in english:
I have tow tables (common index call_id) with a list of surveys in the call_id table and a list of answers in the answers table.

I want to know conditional responses to one question, based on another?

Any help/guidance would be most appreciated.

 

[swampBoogie]

SELECT COUNT(ci.call_id) as cid, a1.answer as A, a2.answer as B
FROM answers a1 INNER JOIN call_info ci
  ON a1.call_id = ci.call_id 
 and a1.question = 1
 inner join answers as a2
  ON ci.call_id = a2.call_id
 and a2.question=2
GROUP BY a2.answer,a1.answer
ORDER BY B, A

You shouldn't use the correlation names in the group by clause as it is
non-standard sql.

 

[wanttoscript]

THank you swampBoogie ...

Your advice is right to the point .. and very helpful to a novice like me!