Join Help

~Bean~ · October 11th, 2005, 12:36 PM

I have 3 tables in my database. The tables are:

tblExams:
examid
examname
etc...

tblExamQuestions
id
examid
questionid

tblQuestions
questionid
questiontext
etc...

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...

Code:

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.poolid

-------------------------
Beware of programmers with screwdrivers...

SqlMenace · October 11th, 2005, 12:43 PM

have you tried distinct or grouping???

âI sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

~Bean~ · October 11th, 2005, 12:49 PM

I tried both but I think they failed because of the column "isused" I am trying to ouput...

I think the Grouping complains about Grouping by a text/ntext value and Distinct still created 2 rows, one for isused=0 (false) and one for isused=1(true)

any suggestions are appreciated!

-------------------------
Beware of programmers with screwdrivers...

jbenson001 · October 11th, 2005, 02:01 PM

Can you post some sample data and expected results?

~Bean~ · October 11th, 2005, 02:13 PM

Sure...

sample data:
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

-------------------------
Beware of programmers with screwdrivers...

jbenson001 · October 11th, 2005, 04:43 PM

   The reason for the duplicates is because the per your example, you can have the same question on many exams. So you will get duplicates. Ex: For question 1, you will get 2 rows, for question 2 you will get 3 rows.
   I have not found a slick way to do it in one sql statement but the code below does work. If you have any questions, let me know:

SELECT q.questionid, questiontext, isused = 1
From tblquestions q
where q.questionid in (select distinct questionid
                       from tblExamQuestions)

Union All

SELECT q.questionid, questiontext, isused = 0
From tblquestions q
where q.questionid NOT in (select distinct questionid
                       from tblExamQuestions)

Jim

~Bean~ · October 12th, 2005, 12:43 PM

thanks Jim one small modification and that worked nicely

-------------------------
Beware of programmers with screwdrivers...

jbenson001 · October 12th, 2005, 01:22 PM

Glad to help :)

Jim