Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ksidj
Starting Member
11 Posts |
 Posted - 2002-07-16 : 11:59:50
|
| I have two tables - contracts and customers.Contracts has a PK contractkey and FK customerkey.There's contract related data in the contract table, and customer related data in the customer table.One of my columns in the contracts table is 'deleted' which is a flag I set for deleted contract (we don't actually want to delete any data). I set the flag deleted=1 if it's marked for deletion.I would like to check to see if a contract is marked for deletion, so that if it IS...the user won't see the entry.I'm using this-SELECT *FROM contracts c INNER JOIN customers ON c.customerkey = customers.customerkeyWHERE c.contractkey = '716' AND c.deleted <> '1'And while contract 716 has <NULL> in the the deleted column, I am getting no results with this query.Can someone tell me what bone-headed mistake I am making?Thanks!Edited by - ksidj on 07/16/2002 12:03:59 |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-07-16 : 12:22:27
|
| Hopefully someone can shed further light on why SQL performs this way, but I have had this same issue and had to modify my SQL statement to require it to look at nulls.SELECT * FROM contracts c INNER JOIN customers ON c.customerkey = customers.customerkey WHERE c.contractkey = '716' AND (c.deleted <> '1' or c.deleted is Null)Another suggestion would be to add a NOT NULL constaint on the deleted column and give it a DEFAULT of 0.Jeremy |
 |
|
|
ksidj
Starting Member
11 Posts |
Posted - 2002-07-16 : 12:27:39
|
| I think I understand-The reason this query doesn't work is that NULL doesn't equate to anything. In other words, NULL values are not included in the result set of "give me everything that doesn't equal X"; saying that it doesn't equal something implies that it CAN equal something else, which is false.Changing the default from NULL to 0 fixes the problem and allows my query to work as expected.Thanks. |
|
|
|
|
|
|
Help with SELECT statement