[SOLVED] Selecting rows based on another table

[ejaboneta]

so i have 2 tables, one with questions, and one with responses. I don't want users to be asked the same questions twice.

 

In the response table, there is an answer id, user id, question id and the answer.

 

In the question table, theres question id, and the question.

 

I want to pick a question that the user hasn't answered yet. Any ideas?

 

[ejaboneta]

Oh yea, version 4.1.....

 

 

and heres an example.

 

under the questions table:

id        question                  priority

1      Favorite Color?                3

2      Favorite Fruit?                  2

3        Country?                      3

4        Gender?                      4

 

Under Answers:

id      question id      answer      user

0              1                blue        22

1              1                red          4

2              3                US          22

 

 

I want the php code and/or query to find questions that user 22 hasn't answered and return one with the highest priority rating. It can find it the other way around, i dont care. So it would find questions 2 and 4 because there is no answers with those id numbers and user 22 and then select question 2 because it's priority is higher.

 

Maybe I'm just tired, but i dont know how where to start. I have some other columns and tables involved in this problem but this is the basic function i'm working on. I plan on having alot of questions and even more answers, so i'm hoping for a solution that will be fast..

[J.Daniels]

You can try to LEFT JOIN your response table and find any question ids that are null.

 

SELECT q.id, q.question FROM questions q LEFT JOIN response r on q.id=r.question_id WHERE r.user=22 AND r.question_id is NULL ORDER BY q.priority

[ejaboneta]

I should mention I'm somewhat of a beginner. I understand the sql and I modified it to fit my database but it looks like the r.user=0 takes out all the Null values which is the opposite of what I want... And besides that, questions may be answered by other users so the r.user column isnt always going to be null.

 

But thanks for the help. I'm gonna play around with it to see if I can come up with something.

 

SELECT q.id, q.question, r.answer, r.user FROM poll_questions q LEFT JOIN poll_responses r ON q.id=r.poll WHERE r.user=0 AND r.id is NULL ORDER BY q.priority

[ejaboneta]

Ok so I think I need something like a WHERE clause before joining.....

 

This doesn't work

SELECT q.id, q.question, r.answer, r.user
FROM poll_questions q 
WHERE r.user = 0
LEFT JOIN poll_responses r ON q.id = r.poll

 

 

 

and adding it to the end, just selects the rows from the combined table.

SELECT q.id, q.question, r.answer, r.user
FROM poll_questions q
LEFT JOIN poll_responses r ON q.id = r.poll
WHERE r.user = 0

[kickstart]

Hi

 

Minor mod to you

 

The idea is that the LEFT JOIN will bring back every question and if there is a machine response for the user then the response as well. However the WHERE clause will exclude any which have had a respose (as the r.id will be not null).

 

The order DESC will bring them back in the order of the most important first. The LIMIT will just bring back the first row.

 

SELECT q.id, q.question, r.answer, r.user 
FROM poll_questions q 
LEFT JOIN poll_responses r ON q.id=r.poll AND r.user=22
WHERE r.id is NULL 
ORDER BY q.priority DESC
LIMIT 0,1

 

All the best

 

Keith

[ejaboneta]

perfect! You have no idea how excited I am right now haha! Thanks to you both! I think I understand joining a little better now.