MySQL Advanced Counting?

[mo0f]

Okay, so I have come across an issue that I cannot seem to resolve. I need a push in the right direction and maybe some starter code. Anyway, I have 3 tables involved, however I was trying to simplify things by using a view created from a join of my two tables MetricsQ and MetricsA which both have MetricIndex as common fields and the view name is QA. I need to iterate through each record in QA and there is 15 rows that I need to compare against a table named Choices. The 15 rows (Fields named QID1 thru QID15) contain values that would be a match for the index in Choices table called ChoiceID. It needs to compare where the Ans field in the Choices table equals another 15 fields (ANS1 thru ANS15). I just need to compare where (QID1 Thru QID15) equals ChoiceID AND where (ANS1 Thru ANS15) equals Ans. Then if its true, add one to a field named score in the MetricsA table. Otherwise increment by zero. So, the following is my attempt to do this code:

SELECT if(QID1=ChoiceID-1 AND ANS1=Ans,1,0) + if(QID2=ChoiceID-1 AND ANS2=Ans,1,0) + if(QID3=ChoiceID-1 AND ANS3=Ans,1,0) + if(QID4=ChoiceID-1 AND ANS4=Ans,1,0)+ if(QID5=ChoiceID-1 AND ANS5=Ans,1,0)+ if(QID6=ChoiceID-1 AND ANS6=Ans,1,0)+ if(QID7=ChoiceID-1 AND ANS7=Ans,1,0)+ if(QID8=ChoiceID-1 AND ANS8=Ans,1,0)+ if(QID9=ChoiceID-1 AND ANS9=Ans,1,0)+ if(QID10=ChoiceID-1 AND ANS10=Ans,1,0)+ if(QID11=ChoiceID-1 AND ANS11=Ans,1,0)+ if(QID12=ChoiceID-1 AND ANS12=Ans,1,0)+ if(QID13=ChoiceID-1 AND ANS13=Ans,1,0)+ if(QID14=ChoiceID-1 AND ANS14=Ans,1,0)+ if(QID15=ChoiceID-1 AND ANS15=Ans,1,0) AS totals FROM QA, Choices

 

It outputs a column and says '87 rows fetched'. Which is the amount of records in the Choices table multiplied by amount of records in the QA view. Attaching a pdf. Top left table is QTable, top right is Choices, bottom left is MetricsQ and bottom right is MetricsA. QA would be a joined table of the bottom two minus the answered/unanswered fields. Any assistance is appreciated. Thank you.

 

[attachment deleted by admin]

[artacus]

You are doing this WAY harder than it needs to be.

 

Join qa on choices. If you don't know how to join tables, google it or try here

 

http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/