J
jacob
You can find my DB schema here:
http://www.geocities.com/jacobpressures/NewDB.JPG
The problem that i've been having is that there are several correct Answers
to a Question. A user does not have to select any answers at all. However if
all the correct answers are not selected, the question is wrong.
I've only found one way of doing this.
http://www.geocities.com/jacobpressures/query1.JPG
I placed it on the internet to reduce clutter and because of formatting.
I can post it here.
Basically this query only allows me to select the info of one employee at a
time. I want to retrieve in the info for all employees. That is the part i
can't do.
Description of query:
The innermost query selects the employee responses (to check for
correctness) for a particular employee or empid (ie 57). Instead of the
actual ResponseID it selects the Responses.AnswerID (r.answerid) to match the
Answers table.
The middle query selects the "Question ID" (QID) for all the questions where
the answer is correct but was NOT found IN or among the r.answerid of the
previous query. In other words, it retrieves those questions where the user
didn't choose all the correct answers by comparing the Answers table, which
has all the correct answers, to the innermost query results. Those QIDs
without answers in the subquery are selected.
The main query selects the User's completed question id (CQID) where the QID
was found to be wrong or missing some correct answers from the previous
queries.
The problem is that if i wanted to select the questions missed for all the
employees, this query will not work. The middle query will look at all the
r.answerid. With 200 employees if one person get it right, regardless of how
many gets it wrong, that question will not be selected because the middle
query just searches to see if all the correct answerids are found in the
subquery.
Also, from my understanding, even if a question is found to be wrong, it
will identify that QID and pass it to the main query. if the main query
selects all the empid <= total, It will select all the Completed questions
(per employee) where the QID from the subqueries match. This will cause all
those questions with that QID to be wrong or assumed wrong.
Thus I have to do each employee one at a time. This cannot be! We have 200
employees.
In case you are wondering, there is another part of this query that i use
with a UNION or converted into INNER JOINS and subqueries that also searches
for WRONG answers (this one part only finds incomplete answers), but that is
simple when searching for data for more than one employees and i kept it out
to keep the query short. For the problem at hand a correlated query is the
only solution i can think of. But i'm new to this also.
I hope I've made this clear.
Please help!!!
Thanks!
http://www.geocities.com/jacobpressures/NewDB.JPG
The problem that i've been having is that there are several correct Answers
to a Question. A user does not have to select any answers at all. However if
all the correct answers are not selected, the question is wrong.
I've only found one way of doing this.
http://www.geocities.com/jacobpressures/query1.JPG
I placed it on the internet to reduce clutter and because of formatting.
I can post it here.
Basically this query only allows me to select the info of one employee at a
time. I want to retrieve in the info for all employees. That is the part i
can't do.
Description of query:
The innermost query selects the employee responses (to check for
correctness) for a particular employee or empid (ie 57). Instead of the
actual ResponseID it selects the Responses.AnswerID (r.answerid) to match the
Answers table.
The middle query selects the "Question ID" (QID) for all the questions where
the answer is correct but was NOT found IN or among the r.answerid of the
previous query. In other words, it retrieves those questions where the user
didn't choose all the correct answers by comparing the Answers table, which
has all the correct answers, to the innermost query results. Those QIDs
without answers in the subquery are selected.
The main query selects the User's completed question id (CQID) where the QID
was found to be wrong or missing some correct answers from the previous
queries.
The problem is that if i wanted to select the questions missed for all the
employees, this query will not work. The middle query will look at all the
r.answerid. With 200 employees if one person get it right, regardless of how
many gets it wrong, that question will not be selected because the middle
query just searches to see if all the correct answerids are found in the
subquery.
Also, from my understanding, even if a question is found to be wrong, it
will identify that QID and pass it to the main query. if the main query
selects all the empid <= total, It will select all the Completed questions
(per employee) where the QID from the subqueries match. This will cause all
those questions with that QID to be wrong or assumed wrong.
Thus I have to do each employee one at a time. This cannot be! We have 200
employees.
In case you are wondering, there is another part of this query that i use
with a UNION or converted into INNER JOINS and subqueries that also searches
for WRONG answers (this one part only finds incomplete answers), but that is
simple when searching for data for more than one employees and i kept it out
to keep the query short. For the problem at hand a correlated query is the
only solution i can think of. But i'm new to this also.
I hope I've made this clear.
Please help!!!
Thanks!