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!
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
