List:General Discussion« Previous MessageNext Message »
From:stig erikson Date:December 5 2002 7:36pm
Subject:EXCEPT, NOT IN, JOIN
View as plain text  
hi
MySQL 3.23.52
could not make my way throuh the JOIN statement.

i would like to accomplish something like what EXCEPT
does in other databases, with a full SQL-statement
after the EXCEPT.

xxxIDref is a foreign key.

first i have a table with users
ID Name
1  John Doe
2  Jane Doe
3  Me Myself

then i have one table with questions.
ID Text
1  What is ...
2  Who is ...
3  Question 3

and i have a table with possible answers
ID QuestionIDref Text 
1  1             Yes
2  1             No
3  2             Me
4  2             You
5  3             Well
6  3             not well

when a user anwers somthing to a question 
the result is saved in a table called answerToQuestion

ID QuestionIDref AnswerIDref PersonIDref
1  1             1           1
2  1             2           2
3  2             3           2



now i would like to find out that person 1 has not
answerd question 2 and 3
and person 2 has not answered to question 3, and that
person 3 has not answerd to any question.

i would like something like (to get person 3's
remaining questions)

SELECT q.ID
FROM   questions AS q1
EXCEPT (
	SELECT q2.ID
	FROM   questions AS q2, answerToQuestion AS atq
	WHERE  atq.PersonIDref=3
	);


however the LEFT JOIN statement in MySQL seems not
allow anything like
WHERE  atq.PersonIDref=3.

the problem is that if i write:
SELECT    q.ID
FROM      questions AS q
LEFT JOIN answerToQuestion ON
q.questionID=answerToQuestion.QuestionIDref
WHERE     resultQuestion.questionIDref IS NULL
ORDER BY  q.questionID;

this does not select the remaining question for a
specific person.

so i would need somthing like:
SELECT    q.ID
FROM      questions AS q
LEFT JOIN answerToQuestion ON
q.questionID=answerToQuestion.QuestionIDref
WHERE     answerToQuestion.questionIDref IS NULL
AND       answerToQuestion.PersonIDref=3
ORDER BY  q.questionID;


but this does not produce any answer.
also tried to use IN instead of AND to select the
current person, but still no luck.


any help is appreciated.
stig

_____________________________________________________
Gratis e-mail resten av livet på www.yahoo.se/mail
Busenkelt!
Thread
EXCEPT, NOT IN, JOINstig erikson5 Dec