Prasad Avunoori is pointing you in the right direction - You need somewhere to store
how each person has answered each question.
Your current results table is attempting to store summary results, but you currently have nowhere to store the detail!
Here's is a worked example ... warning - it's rather long
Create a table that will hold the detail. It needs to link to the Person who took the quiz and and also link to the Question that was answered.
For example...
CREATE TABLE Results(
P_ID int not null,
Q_ID int not null,
ANSGIVEN int not null,
ISCORRECT bit not null default 0,
foreign key (P_ID) references Person(P_ID),
foreign key (Q_ID) references Question(Q_ID),
primary key (Q_ID, P_ID)
)
The foreign key columns P_ID and Q_ID are fundamental to the joins you will create. I've included a Primary key which isn't strictly necessary at this stage, but is always worth considering (for performance)
Before you can create that table you will need somewhere to hold the summary results and Person details
I've created a new table here, but this is similar to your original Results table
CREATE TABLE Person(
P_ID int identity(1,1),
PNAME varchar(50) not null,
NUMQUESTIONS int not null default 0,
NUMCORRECT int not null default 0,
PC_CORRECT float not null default 0.0,
PRIMARY KEY (P_ID)
)
And I created this Questions table so that I could run these queries (to follow)
CREATE TABLE Question(
Q_ID int identity(1,1),
QUESTION varchar(50) not null,
ANSWER1 varchar(50) not null,
ANSWER2 varchar(50) not null,
ANSWER3 varchar(50) not null,
ANSWER4 varchar(50) not null,
CORRECT int not null,
PRIMARY KEY(Q_ID)
)
Let's create some test data.
Set up some Users of this quiz ...
INSERT INTO Person (PNAME) VALUES ('Adam')
INSERT INTO Person (PNAME) VALUES ('Benjamin')
INSERT INTO Person (PNAME) VALUES ('Caleb')
INSERT INTO Person (PNAME) VALUES ('Daniel')
INSERT INTO Person (PNAME) VALUES ('Ezra')
Point of interest - I've only provided the PNAME column as all the other values have default 0
Now set up some questions...
INSERT INTO Question VALUES ('Question 1', 'A', 'B', 'C', 'D', 1)
INSERT INTO Question VALUES ('Question 2', 'A', 'B', 'C', 'D', 2)
INSERT INTO Question VALUES ('Question 3', 'A', 'B', 'C', 'D', 3)
INSERT INTO Question VALUES ('Question 4', 'A', 'B', 'C', 'D', 4)
Point of interest - because I'm providing all of the values except Q_ID (which I should never attempt to insert) I don't need to list the columns I'm using - unlike the insert into Person.
Finally, Set up some results
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,2,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,3,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,4,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,2,2)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,3,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,4,4)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,2,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,1,4)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,2,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,3,2)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,4,1)
Let's look at those results. Point of interest, I'm going to give each table name an
ALIAS
e.g. 'Results' will be called 'R' and 'Question' will be called 'Q'
SELECT P.PNAME, R.ANSGIVEN,Q.CORRECT, R.ISCORRECT
FROM Results R
INNER JOIN Question Q ON R.Q_ID=Q.Q_ID
INNER JOIN Person P ON R.P_ID=P.P_ID
ORDER BY R.P_ID
which gives us
PNAME ANSGIVEN CORRECT ISCORRECT
Adam 1 1 0
Adam 1 2 0
Adam 3 3 0
Adam 1 4 0
Benjamin 4 4 0
Benjamin 3 3 0
Benjamin 2 2 0
Benjamin 1 1 0
Caleb 1 1 0
Caleb 1 2 0
Daniel 3 2 0
Daniel 4 1 0
Daniel 2 3 0
Daniel 1 4 0
Hmm... when I was inserting the Results of the quiz I didn't look up the correct answer, so all my ISCORRECT fields are still 0, which means false or wrong. And that's not right!
We'll correct that now ... However, this query is handy for showing you how to get values from a joined table to update another table.
It also demonstrates why I used Table Aliases - so much clearer don't you think?
UPDATE R
SET ISCORRECT = CASE WHEN R.ANSGIVEN = Q.CORRECT THEN 1 ELSE 0 END
FROM Results R
INNER JOIN Question Q ON R.Q_ID=Q.Q_ID
Now we're ready to get the Results you've been waiting for! Let's try this query...
SELECT PNAME, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT
FROM Person P
INNER JOIN Results R ON R.P_ID=P.P_ID
INNER JOIN (SELECT P_ID, COUNT(*) AS NUMCORRECT
FROM Results WHERE ISCORRECT = 1
GROUP BY P_ID
) AS C ON C.P_ID=P.P_ID
GROUP BY PNAME, C.NUMCORRECT
ORDER BY PNAME
Point of interest, that second INNER JOIN isn't to a table, but to another query. I've done that because I only want to count the correct answers in that bit, but I still need all of the results for the total count of questions answered
And that produces ...
PNAME NUMQUESTIONS NUMCORRECT
Adam 4 2
Benjamin 4 4
Caleb 2 1
Hang on minute, where's Ezra? Where's Daniel?
Ok - Ezra didn't answer any questions, but Daniel answered all 4 questions. He got them all wrong, but he did answer them!
The problem is because I used
INNER JOIN
on the sub-query. That sub-query only returns a value
WHERE ISCORRECT = 1
So neither Daniel nor Ezra is going to appear in that list.
So I need to do a
LEFT OUTER JOIN
onto the sub-query to make sure Daniel ... well ... isn't left out!
SELECT PNAME, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT
FROM Person P
INNER JOIN Results R ON R.P_ID=P.P_ID
LEFT OUTER JOIN (SELECT P_ID, COUNT(*) AS NUMCORRECT
FROM Results WHERE ISCORRECT = 1
GROUP BY P_ID
) AS C ON C.P_ID=P.P_ID
GROUP BY PNAME, C.NUMCORRECT
ORDER BY PNAME
And the results are ...
PNAME NUMQUESTIONS NUMCORRECT
Adam 4 2
Benjamin 4 4
Caleb 2 1
Daniel 4 (null)
That's better. Ezra still isn't in the list, but that's ok because he didn't answer any questions. But if I
did want to see his results as well I'd have to change that first
INNER JOIN
to a
LEFT OUTER JOIN
as well.
Let's convert that select into an update so we can get the results onto our Person table.
We will wrap up that entire query into a sub query, we will "call" the results of that sub-query RX, and then JOIN RX to the Person table via the P_ID columns.
UPDATE P SET NUMQUESTIONS=RX.NUMQUESTIONS,
NUMCORRECT = RX.NUMCORRECT
FROM Person P
INNER JOIN
(SELECT P.P_ID, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT AS NUMCORRECT
FROM Person P
INNER JOIN Results R ON R.P_ID=P.P_ID
LEFT OUTER JOIN
(SELECT P_ID, COUNT(*) AS NUMCORRECT
FROM Results WHERE ISCORRECT = 1
GROUP BY P_ID
) AS C ON C.P_ID=P.P_ID
GROUP BY P.P_ID, C.NUMCORRECT
) AS RX ON P.P_ID=RX.P_ID
*Groan* - now I get an error
Quote:
Cannot insert the value NULL into column 'NUMCORRECT', table 'Person'; column does not allow nulls.
Remember Daniel's results earlier came out as
PNAME NUMQUESTIONS NUMCORRECT
Daniel 4 (null)
and we defined those columns as
NUMQUESTIONS int <b>not null</b> default 0,
NUMCORRECT int not null default 0,
We could go back to the table and remove the 'not null default 0' bits or we can handle it here and now with the ISNULL function. I'll do that ...
UPDATE P SET NUMQUESTIONS=ISNULL(RX.NUMQUESTIONS,0),
NUMCORRECT = ISNULL(RX.NUMCORRECT,0)
FROM Person P
INNER JOIN
(SELECT P.P_ID, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT AS NUMCORRECT
FROM Person P
INNER JOIN Results R ON R.P_ID=P.P_ID
LEFT OUTER JOIN
(SELECT P_ID, COUNT(*) AS NUMCORRECT
FROM Results WHERE ISCORRECT = 1
GROUP BY P_ID
) AS C ON C.P_ID=P.P_ID
GROUP BY P.P_ID, C.NUMCORRECT
) AS RX ON P.P_ID=RX.P_ID
Now let's look at the Person table and see what we have ...
SELECT * FROM Person P order by PNAME
which gives
P_ID PNAME NUMQUESTIONS NUMCORRECT PC_CORRECT
1 Adam 4 2 0
2 Benjamin 4 4 0
3 Caleb 2 1 0
4 Daniel 4 0 0
5 Ezra 0 0 0
Notice I haven't updated PC_CORRECT - that's an exercise for you - it can be added into the same update query above, or done separately in your C# program.
Hint - Watch out for
null
values and division by zero!
[If I have inadvertently offended anyone by the choice of names in the Person table, be aware they are actually characters from the 1954 film "Seven Brides For Seven Brothers"
[^] and not from any other book]