Query Help

[intervelopment]

Good Morning,

I have a bit of an issue with a script im writing and would really appreciate some help.

I have 2 tables, the first table has id, question_id, email_address and timestamp.

The second table has question_id and question.

What i need to be able to do, is get all the question_id's from the first table where the email address is lets say [e-mail]

Then i need to search through the second table to find a random question with a question_id that is not listed in the results from the first query and print it out.

I have been looking around for quite some time but cannot seem to nail it.

If anyone could help, that would be greatly appreciated.

Cheers

[bpat1434]

You can do this in two queries, or with a join.

With two queries you would do something like:

SELECT question_id, question
FROM table2
WHERE question_id NOT IN (
        SELECT question_id 
        FROM table1
        WHERE email = '[e-mail]'
)
ORDER BY RAND()

Now, you could always do a join and find where the the question_id column in table1 is null (or empty).

SELECT t2.question_id, t2.question
FROM table2 AS t2
LEFT JOIN table1 AS t1
    ON t2.question_id = t1.question_id
WHERE t1.question_id IS NULL
ORDER BY RAND()

Basically with the join we're saying that we want all the rows from table2 even if there aren't corresponding rows in table1, then we're filtering out only those rows in table2 that aren't in table1 and ordering by random.

So if you have the following sample set:

table1
+--------------------------------------------------------------------+
| id | question_id | email_address          | timestamp              |
+--------------------------------------------------------------------+
| 1  | 1           | [e-mail]          | 2009-08-01 12:30:00    |
| 3  | 8           | [e-mail]          | 2009-08-02 17:12:38    |
+--------------------------------------------------------------------+

table2
+--------------------------------------------------------------------+
| question_id | question                                             |
+--------------------------------------------------------------------+
| 1           | How much wood could a wood chuck chuck if a wood ... |
| 2           | What is the average air speed of a Swallow?          |
| 8           | If a fly is flying in a car but not moving forwa ... |
+--------------------------------------------------------------------+

Both queries should afford you this result:

+--------------------------------------------------------------------+
| question_id | question                                             |
+--------------------------------------------------------------------+
| 2           | What is the average air speed of a Swallow?          |
+--------------------------------------------------------------------+

Hope that helps.