[PHP-DB] Re: php-db Finding records with NULL values
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_idQuestion 1Question 1 2Question 2 3Question 3 4Question 4 Response Table R_idMember_idQ_idResponse 111Yes 213No 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 - 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
Re: [PHP-DB] Re: php-db Finding records with NULL values
Robin 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_idQuestion 1Question 1 2Question 2 3Question 3 4Question 4 Response Table R_idMember_idQ_idResponse 111Yes 213No 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 Check out OUTER JOINs (RTFM: 6.4.1.1 JOIN Syntax) =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Finding records with NULL values
Hi Robin, Use a standard select like you normally would, and in your where clause, use where [field] is null : select field1[, field2, ...] from table1[, table2, ...] where fieldx is null [...] -bsh Robin McKenzie wrote: 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 -- /-=[ BILLY S HALSEY ]=--\ | Member of Technical Staff, Sun Microsystems, Inc. ESP Solaris SW | | All opinions and technical advice offered in this message are my | | own and not necessarily endorsed by my employer. | \--=[ [EMAIL PROTECTED] ]=/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php-db Finding records with NULL values
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 - 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
