Need Join or Union

[vBean]

I have 3 tables in my database. Like:

tblExams:
examid      examname
1             Test One
2             Test Two
3             Mid-Term
etc.
tblExamQuestions
id          examid             questionid
1             1                   1
2             2                   2
3             2                   1
4             2                   2
5             3                   2
etc.

tblQuestions
questionid            questiontext
1                         What color is the sky?
2                         How old are you?
3                         Can you answer this?
4                         Is this question usused?
etc...

Then my query would return...
questionid           questiontext                       isused
1                    What color is the sky?             1
2                    How old are you?                   1
3                    Can you answer this?               0
4                    Is this question usused?           0

I need a query to return a list of all questions, regardless of whether they have been used in an exam, but also reutn a field denoting that they have been used. So far I have this SQL but it returns duplicate values...it seems no matter hwo I tweak it I cant get what I want...

SELECT q.questionid, questiontext, "isused"= 
CASE WHEN e.name IS NULL THEN
    0
ELSE
    1
END
FROM tblQuestions As q
LEFT OUTER JOIN tblExamQuestions As eq ON eq.questionid = q.id
LEFT OUTER JOIN tblExam As e ON e.id = eq.examid

 

[r937]

add DISTINCT after SELECT

remove the second LEFT OUTER JOIN and perform the test inside the CASE on eq.questionid instead

r937.com | rudy.ca

 

[jbenson001]

I answered this in the Wrox forum you posted in...

Jim

 

[vBean]

thnks!

 

[jbenson001]

No problem... Jim