Menu
I don't know if I am doing the below method correctly when using sql but here it goes.
I want to display all exam details (exams details (Also know as Session), questions in exam, answers, penalty, images, videos etc) But the way I am trying to do it is one big query but it is not working as no rows are being displayed.
Below are what I want to display for one exam (and only one exam):
Below show the fields each section above require from database:
Exam Details
SessionId, SessionName, SessionDuration, TotalMarks, SessionWeight
All questions in Exam
QuestionId, QuestionNo, QuestionContent, NoofAnswers, QuestionMarks, OptionId, OptionType, ReplyId, ReplyType
All Answers to each question and marks each answer worth
AnswerId, Answer, AnswerMarks
PenaltyEnabled
PenaltyAnswerId, PenaltyAnswer, PenaltyMarks
ImageId, ImageFile
VideoId VideoFile
AudioId, AudioFile
ModuleId, ModuleNo, ModuleName
StudentId
My question is how should the SQL code be written in order for all these data to be shown? Do I just need one big query or a lot of little queries? My attempt is at bottom of question but below is the database tables which shows details dealing with one exam and all its details.
TABLES:
Student
StudentId (PK) StudentForename StudentSurname
1 James Parker
Student_Session
SessionId (FK) StudentId (FK)
1 1
Session
SessionId (PK) SessionName SessionDuration TotalMarks SessionWeight ModuleId (FK)
1 AAA 01:00:00 30 20 1
Module
ModuleId (PK) ModuleNo ModuleName
1 CHI2513 ICT
Question
SessionId FK) QuestionId (PK) QuestionNo QuestionContent NoofAnswers QuestionMarks OptionId (FK) ReplyId (FK)
1 4 1 Question 1 1 5 1 1
1 5 2 Question 2 1 3 2 1
1 6 3 Question 3 2 6 2 2
1 7 4 Question 4 3 7 5 2
1 8 5 Question 5 1 9 5 1
Answer:
AnswerId (PK) Answer QuestionId (FK)
1 A 4
2 C 5
3 A 6
4 B 6
5 B 7
6 D 7
7 E 7
8 G 8
Individual Answer
IndividualId (PK) AnswerId (FK) AnswerMarks
1 1 3
2 2 5
3 3 3
4 4 3
5 5 2
6 6 2
7 7 3
8 8 9
Penalty
PenaltyId(PK) SessionId (FK) PenaltyEnalbed
1 1 1
PenaltyMarks
PenaltyAnswerId (PK) PenaltyAnswer PenaltyMarks QuestionId (FK)
1 B 1 4
2 C 1 4
3 A 1 5
4 B 1 5
5 D 1 5
6 C 2 6
7 D 2 6
8 A 1 7
9 C 1 7
10 F 1 7
11 G 1 7
12 A 0 8
13 B 0 8
14 C 1 8
15 D 1 8
16 E 1 8
17 F 0 8
Reply
ReplyId (PK) ReplyType
1 Single
2 Multiple
Option_Table
OptionId (PK) OptionType
1 A-C
2 A-D
3 A-E
4 A-F
5 A-G
Image
ImageId (PK) ImageFile
1 ImageFile/Tulips.png
2 ImageFile/Daisys.png
Video
VideoId (PK) VideoFile
1 VideoFile/Speech.png
Audio
AudioId (PK) AudioFile
1 AudioFile/Song.png
Image_Question
ImageQuestionId (PK) ImageId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 2
Video_Question
VideoQuestionId (PK) VideoId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 4
Audio_Question
AudioQuestionId (PK) AudioId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 5
Below was my failed attempt:
SELECT s.SessionId,
SessionName,
SessionDuration,
TotalMarks,
SessionWeight,
q.QuestionId,
q.QuestionNo,
QuestionContent,
QuestionMarks,
q.OptionId,
OptionType,
q.ReplyId,
ReplyType,
a.AnswerId,
Answer,
NoofAnswers,
AnswerMarks,
PenaltyEnabled,
PenaltyAnswerId,
PenaltyAnswer,
PenaltyMarks,
i.ImageId,
au.AudioId,
v.VideoId,
ImageFile,
AudioFile,
VideoFile,
s.ModuleId,
ModuleNo,
ModuleName,
ss.StudentId
FROM Student st
INNER JOIN Student_Session ss
ON st.StudentId = ss.StudentId
INNER JOIN Session s
ON ss.SessionId = s.SessionId
INNER JOIN Question q
ON s.SessionId = q.SessionId
INNER JOIN Answer a
ON q.QuestionId = a.AnswerId
INNER JOIN Individual_Answer ia
ON a.AnswerId = ia.AnswerId
LEFT JOIN Module m
ON s.ModuleId = m.ModuleId
LEFT JOIN Penalty p
ON q.SessionId = p.SessionId
LEFT JOIN Option_Table o
ON q.OptionId = o.OptionId
LEFT JOIN Reply r
ON q.ReplyId = r.ReplyId
LEFT JOIN Penalty_Marks pm
ON q.QuestionId = pm.QuestionId
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionNo
INNER JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionId = aq.QuestionNo
INNER JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionId = vq.QuestionNo
INNER JOIN Video v
ON vq.VideoId = v.VideoId
WHERE s.SessionId = 1
ORDER BY q.QuestionId
UPDATE:
Will the code below be able to insert QuestionId into Image_Question Table after questions submitted: (This is something I found on SO but updated to my table design)
ImageQuestionToken
{
ImageToken (PK auto)
SessionId
}
Image{
ImageId
ImageFile
}
Image_Question
{
Image_QuestionId
ImageId FK references Image(ImageId)
QuestionId FK references Question(QuestionId)
}
Question
{
QuestionId (PK Auto)
QuestionNo
QuestionContent
....
}
TempImage
{
ImageToken FK references ImageQuestionToken(ImageToken)
ImageFile
}
And the logic like so:
395
While I am not 100% sure of the exact result that you want. Based on your existing query the issue is that you are using INNER JOIN on some of the tables when you should be using LEFT JOIN.
In your existing query you are using the following:
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionNo
INNER JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionId = aq.QuestionNo
INNER JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionId = vq.QuestionNo
INNER JOIN Video v
ON vq.VideoId = v.VideoId
The problem is that the INNER JOIN is checking for matching records on all fields but you might not have a record that exists in the image, audio or video tables so it is not returning anything.
Based on your details your query should be similar to this:
select st.studentid,
s.sessionid,
s.sessionname,
s.sessionduration,
s.totalmarks,
s.sessionweight,
q.questionid,
q.questionno,
q.questioncontent,
q.noofanswers,
q.questionmarks,
q.optionid,
ot.optiontype,
q.replyid,
r.replytype,
a.answerid,
a.answer,
ia.answermarks,
p.penaltyenabled,
pm.penaltyanswerid,
pm.penaltyanswer,
pm.penaltymarks,
i.imageid,
i.imagefile,
v.videoid,
v.videofile,
ad.audioid,
ad.audiofile,
m.moduleid,
m.moduleno,
m.modulename
from Student st
inner join Student_Session ss
on st.studentid = ss.studentid
inner join session s
on ss.sessionid = s.sessionid
inner join question q
on s.sessionid = q.sessionid
inner join answer a
on q.questionid = a.questionid
inner join Individual_Answer ia
on a.answerid = ia.answerid
left join Option_Table ot
on q.optionid = ot.optionid
left join reply r
on q.replyid = r.replyid
left join module m
on s.moduleid = m.moduleid
left join Penalty p
on s.sessionid = p.sessionid
left join penalty_marks pm
on q.questionid = pm.questionid
left join image_question iq -- note I am joining on both session and question
on s.sessionid = iq.sessionid
and q.questionid = iq.questionno
left join image i -- this should be a left join not inner join
on iq.imageid = i.imageid
left join video_question vq -- note I am joining on both session and question
on s.sessionid = vq.sessionid
and q.questionid = vq.questionno
left join video v -- this should be a left join not inner join
on vq.videoid = v.videoid
left join audio_question aq -- note I am joining on both session and question
on s.sessionid = aq.sessionid
and q.questionid = aq.questionno
left join audio ad -- this should be a left join not inner join
on aq.audioid = ad.audioid
where s.SessionId = 1
order by q.QuestionId
See SQL Fiddle with Demo.
This is returning all of the data that you have requested above. Sample:
| STUDENTID | SESSIONID | SESSIONNAME | SESSIONDURATION | TOTALMARKS | SESSIONWEIGHT | QUESTIONID | QUESTIONNO | QUESTIONCONTENT | NOOFANSWERS | QUESTIONMARKS | OPTIONID | OPTIONTYPE | REPLYID | REPLYTYPE | ANSWERID | ANSWER | ANSWERMARKS | PENALTYENABLED | PENALTYANSWERID | PENALTYANSWER | PENALTYMARKS | IMAGEID | IMAGEFILE | VIDEOID | VIDEOFILE | AUDIOID | AUDIOFILE | MODULEID | MODULENO | MODULENAME |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 4 | 1 | Question 1 | 1 | 5 | 1 | A-C | 1 | Single | 1 | A | 3 | 1 | 1 | B | 1 | (null) | (null) | 1 | VideoFile/Speech.png | (null) | (null) | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 4 | 1 | Question 1 | 1 | 5 | 1 | A-C | 1 | Single | 1 | A | 3 | 1 | 2 | C | 1 | (null) | (null) | 1 | VideoFile/Speech.png | (null) | (null) | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 3 | A | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 5 | D | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 4 | B | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
