That may work for a single field in a record that is NULL, but I'm trying to
join two tables, and report the lines that do not have an entry in the
second thus:
Question Table
Q_id Question
1 Question 1
2 Question 2
3 Question 3
4 Question 4
Response Table
R_id Member_id Q_id Response
1 1 1 Yes
2 1 3 No
So, member 1 has answered questions 1 and 3 - I want to find how to get the
q_ids of the remaining questions...
Q_id
2
4
Something like:
SELECT q.q_id
FROM questions q, responses r
WHERE
q.q_id=r.q_id
AND
r.member_id='1'
AND
r.id=NULL % NB This doesn't work... :(
Or maybe it's better by taking the questions they have done,
SELECT q_id FROM responses where member='1'
and somehow subtracting this from the complete list,
SELECT q_id FROM questions
Any clues?
Robin
"Frank Flynn" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Pretty easy really
>
> SELECT * FROM foo WHERE bar is NULL
>
> - DON'T use quotes "NULL" is a valid string it is not the same as NULL
> - Some DBMS's allow you to say bar = NULL but technically this is wrong
> because NULL is undefined and cannot be equal to anything not even another
> NULL.
> - Also "" is not NULL - be sure you blank fields are NULL and not ""
> - A clever way to search for both "" and NULL in the same query is:
>
> SELECT * FROM foo WHERE NOT bar > ""
>
> This may not work in all DBMS's but give it a try; OR would work too.
>
> Good Luck,
> Frank
>
> On 2/26/02 2:20 PM, "[EMAIL PROTECTED]"
> <[EMAIL PROTECTED]> wrote:
>
> > From: "Robin McKenzie" <[EMAIL PROTECTED]>
> > Date: Tue, 26 Feb 2002 21:49:00 -0000
> > To: [EMAIL PROTECTED]
> > Subject: Finding records with NULL values
> >
> >
> >
> > I have a table of questions, and a table of responses (initially empty)
> > which gets updated every time an answer is submitted, with the question
> > number, member id and response.
> >
> > I wish to produce a SELECT query that will find the questions that
haven't
> > been answered by a particular member, i.e. a query that finds the
questions
> > that DO NOT have an entry in responses. How can I do this?
> >
> > Regards,
> >
> > Robin
> >
>
>
> --
> Frank Flynn
> Poet, Artist & Mystic
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
