select query using joins and subquery.

[student]

tblQuestion A


qId(pk) Question catId
10 Which book is this? 20
11 Who is this person? 20
12 Where were you born? 20
13 How to build a fence? 25
14 Why did he say that? 20
15 Who is her role model? 20



tblSessionQuessions B
SqId(pk) sessionId qId_Correct qId_Incorrect

40 1 10
41 1 11
42 1 12
43 1 15
44 3 15

where the 2 tables are using referential integrity on
B.qId_Correct and B.qid _Incorrect on A.qId

I wan't to select questions from tblQuestion which belong
to a specific category (e.g. catId=20) and (for a
particular session (e.g. sessionId =1) that question is
not there in qId_Correct. So questions with qId 11,12,14
should be selected.
How do I accomplish this?

 

[Tom Ellison]

Dear Student:

SELECT A.*
FROM tblQuestion A
LEFT JOIN (SELECT * tblSessionQuessions WHERE SessionId = 1) B
ON B.qld_Correct = A.qld
WHERE A.catID = 20
AND B.qld_Correct IS NULL

Probably the biggest "trick" is to be sure to filter the SessionId out
using the subquery inside the join.

tblQuestion A


qId(pk) Question catId
10 Which book is this? 20
11 Who is this person? 20
12 Where were you born? 20
13 How to build a fence? 25
14 Why did he say that? 20
15 Who is her role model? 20



tblSessionQuessions B
SqId(pk) sessionId qId_Correct qId_Incorrect

40 1 10
41 1 11
42 1 12
43 1 15
44 3 15

where the 2 tables are using referential integrity on
B.qId_Correct and B.qid _Incorrect on A.qId

I wan't to select questions from tblQuestion which belong
to a specific category (e.g. catId=20) and (for a
particular session (e.g. sessionId =1) that question is
not there in qId_Correct. So questions with qId 11,12,14
should be selected.
How do I accomplish this?

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts