Click here to Skip to main content
15,861,168 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have two tables:
questionbank table
qustion answer1 answer2 answer3 answer4 correct_ans sub_name


result table
username name correctanswer totalquestions percentage result sub_name

i want to perform inner join between these two so that result read from questionbank table and store in result table how i do it in sql server 2008.please help.

also i have the code for c#:

SQL
SqlCommand cmd = new SqlCommand(@"SELECT COUNT(result.username) AS correct_ans
                                            FROM  result INNER JOIN
                                            questionbank ON result.Q_id = questionbank.Q_id AND result.User_Ans = questionbank.Correct_Ans AND result.username = " + username + " ");
        SqlCommand cmd1 = new SqlCommand(@"SELECT COUNT(Q_id) AS totalquestions
                                               FROM   questionbank";);
Posted
Updated 29-May-14 21:20pm
v2
Comments
Telstra 30-May-14 3:25am    
which is primary key and foreign key in these two table?
Prasad Avunoori 30-May-14 3:28am    
Explain the total work flow. I think your tables are not well designed.There should be one more table Exam(UserName,Question,AnswerOpted etc.) Then only we can update Result table.
Member 10821047 30-May-14 3:37am    
i think you are write but then how can i do read from these table to store result in result table
Prasad Avunoori 30-May-14 5:09am    
Add one more table to your database design with name : "EXAM"
Fields:Id,UserName,SubjectId,QuestionId,OptedAnswer).
TrushnaK 30-May-14 3:43am    
Is ther any relation between those two tables?
In terms of foreign key..

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...
SQL
-- This holds the individual results by Person by Question
CREATE TABLE Results(
	P_ID int not null,	-- this will link to a (new) Person table
	Q_ID int not null,	-- this will link to the Question table
	ANSGIVEN int not null,	-- The response that the person gave to the question
	ISCORRECT bit not null default 0,	-- Whether or not the response was correct (1) or wrong (0)
	foreign key (P_ID) references Person(P_ID),	-- Note I've used the simplest definition of keys for simplicity
	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
SQL
-- Will hold summary details of the people answering questions
CREATE TABLE Person(
	P_ID int identity(1,1),		-- Unique identifier is automatically generated for me
	PNAME varchar(50) not null,	-- Person's name
	NUMQUESTIONS int not null default 0,	-- How many questions they have answered - starts at 0
	NUMCORRECT int not null default 0,		-- How many questions they got right
	PC_CORRECT float not null default 0.0,	-- Percentage correct
	PRIMARY KEY (P_ID)
)
And I created this Questions table so that I could run these queries (to follow)
SQL
-- Will hold the questions, answers and number of correct answer
CREATE TABLE Question(
	Q_ID int identity(1,1),	-- Unique question number is automatically generated for me
	QUESTION varchar(50) not null,	-- The question
	ANSWER1 varchar(50) not null,	-- Possible answers ... must have all four ...
	ANSWER2 varchar(50) not null,
	ANSWER3 varchar(50) not null,
	ANSWER4 varchar(50) not null,
	CORRECT int not null,	-- The number of the correct answer
	PRIMARY KEY(Q_ID)
)
Let's create some test data.
Set up some Users of this quiz ...
SQL
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...
SQL
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
SQL
-- Adam's answers - Some right some wrong
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)

-- Benjamin's Results - All right!
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)

-- Caleb's results - he didn't finish
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,2,1)

-- Daniel's results - All wrong :-(
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)

-- No results for Ezra, he hasn't started yet
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'
SQL
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?
SQL
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...
SQL
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!
SQL
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.
SQL
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
XML
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 ...
SQL
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]
 
Share this answer
 
Comments
Member 10821047 30-May-14 9:58am    
that will surely helps alot,but i have a question that there is a error showing "cannot insert Null values to the column " and when i uncheck allow Null in that column,but when i try to save that table,it shows error that you have to recreate or drop that table to make changes.what will i do.do i have to drop and recreate that table.
Simplest way just create a view and use view name in place of your table name,
and when you creating your view from sql server you will find joining code also,
enjoy coding
accept it if its work for you
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900