SELECTING using JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

SELECTING using JOIN

Post 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?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Re: SELECTING using JOIN

Post 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 :)
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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!
Post Reply