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

Reply via email to