Page 1 of 1

SELECTING using JOIN

Posted: Wed Aug 15, 2007 2:36 am
by shivam0101
I have two tables,


1 question_table (will be entered by admin)
2 answer_table (will be entered by clients)

question_table

question_id
question


answer_table

member_id
question_id


I want to display all the questions which the members have not taken that question.

I tried using

Code: Select all

"SELECT * FROM question_table, answer_table WHERE answer_table.member_id !=$memberID AND question_table.question_id !=answer_table.question_id";
i am getting

Code: Select all

$memberID
from session of the login user

How to display the questions?

Re: SELECTING using JOIN

Posted: Wed Aug 15, 2007 11:16 am
by ReverendDexter
shivam0101 wrote:I have two tables,


1 question_table (will be entered by admin)
2 answer_table (will be entered by clients)

question_table

question_id
question


answer_table

member_id
question_id


I want to display all the questions which the members have not taken that question.

I tried using

Code: Select all

"SELECT * FROM question_table, answer_table WHERE answer_table.member_id !=$memberID AND question_table.question_id !=answer_table.question_id";
i am getting

Code: Select all

$memberID
from session of the login user

How to display the questions?
I'd try something along the lines of:

Code: Select all

SELECT question
FROM question_table
WHERE question_id NOT IN (SELECT question_id FROM answer_table)
You'll probably want to double check my syntax on that :)

Posted: Wed Aug 15, 2007 11:21 am
by ReverendDexter
Err, I reread your post. My original query will give you all of the questions that no member has answered, and I think what you want is all the questions that a specific member hasn't answered.

That would be more like:

Code: Select all

SELECT question
FROM question_table
WHERE question_id NOT IN (SELECT question_id FROM answer table WHERE member_id = $memberID)
Hope it helps!