asked on

Looping through results?

Experts-

I have a table that holds answers and another table that holds student responses. The answer table can have multiple 'M' answers for an item(question). I'm trying to figure out how many 'M' responses each student got for a particular question.


<cfquery name="getAnswers" datasource="#request.datasource#">
SELECT itemID, answer, answerID
FROM answers a
INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
</cfquery>


<cfquery name="getStudentResponse" datasource="#request.datasource#">
SELECT *
FROM studentresponse
WHERE itemsetID = 60
ORDER BY studentID
</cfquery>

Can someone tell me the best way to get a count for each individual student? Initially I had written a query that had the count function, but with the multiple 'M' responses for an individual ItemID(question) it was throwing my counts off.

Any help would be appreciated

Thanks
nick

_agx_

> The answer table can have multiple 'M' answers for an item(question)

I don't quite understand that. It should be a simple COUNT.

Can you post a small sample of the data, and what counts you expect? Also what's your db again?

gdemaria

Right, more information please...

it seems you're looking for a count of answers (of type "M") for each question.. .but I dont' see the questions table in your queries...

So, it's a count of M-type answers for each question for each student.. .so it would be like this...

Steve
Question A - 10
Question B - 7
Bob
Question A - 12
Question B - 4

where the number is the count of questions that have type M

Please let us know...

nmarano

ASKER

agx-

So in my answer table I have....

itemId answer answerID
882 E C
882 A M
882 B M
882 C m
882 D M
883 A C
883 B M
883 D M
883 E M
884 A C
884 B M
885 B C
886 C M
886 D C
Studentresponse table

sudentId response itemID
21 D 882
21 E 883
21 A 884
21 B 885
21 C 886

So based on this, studentID 21 should have a count of 3 for itemID 882,883,886

Using MYSQL

nmarano

ASKER

Hi Maria-

So as my example of data above, there will be more than one student that I will be running the queries through the get the count. Maybe I'm just over thinking this and it's much simpler than I am making it out to be?

_agx_

Untested, but something like this should give you the "M" count for all students with at least 1 matching answer in the desired set

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM studentresponse sr
INNER JOIN answers a ON a.ItemID = sr.ItemID
INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID, sr.ItemID

nmarano

ASKER

agx- Thanks I'll try it out

Nick

_agx_

Correction. I think you need to join on the "response/answer" column too

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM studentresponse sr
INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID, sr.ItemID

nmarano

ASKER

So I added And where sr.itemsetID = 60 and removed sr.itemID in Group BY so now I have this...

SELECT sr.StudentID, sr.ItemID, COUNT(*) AS TotalNum
FROM studentresponse sr
INNER JOIN answers a ON a.ItemID = sr.ItemID AND a.answer = sr.Response
INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND sr.itemsetID = 60
AND a.answerID ='M'
GROUP BY sr.StudentID

Which I think is what I am looking for, but will do a tad more testing and let you know in a bit...

Thanks for the help

Nick

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmarano

ASKER

Agx-

Thanks! I was over thinking this. Thanks for the help

Nick

_agx_

> and removed sr.itemID in Group BY

Just curious - why? Were you getting different results? If not, I'd leave it in as a good practice. Most db's wouldn't let you exclude it anyway.

nmarano

ASKER

agx- removed it because it was not given me the results that I was expecting. Also, removed it and because I didn't necessarily need it group by itemID as much as i needed it by StudentID.

Thanks again for you help

_agx_

Ah, ok. If you don't really need the results grouped by "itemID" you might try dropping it from the SELECT list. See if you get the same results. Just a thought.

-Later :)