Getting rid of nulls in a view result statement

[Torp23]

I have a view that contains nulls in a column when there is no data. I am calling the view from an ASP page with a select statement and a where clause that specifies the selection of records that match a text string. The query returns all the records that match the string, but also all nulls as well. I tried the "IS NOT NULL" statement but all records with nulls in the field are still returned.

What do I need to do to pull all records that match a string from a view, without the records that have a null in that field?

Scott

 

[cheyney]

Post your query and select statement so people can see it.

IS NOT NULL should work (if they are actually null values...could they be strings that read NULL?)

cheyney

 

[Torp23]

After working on this all morning I learned that (after cleaning up some statements) writing a SQL statement (SQL 2000) against a view that joins multiple tables will return records with null values in the queried field when the queried field is from the 'child' table. If the query references the 'parent' table's value, it will return the correct values without nulls.

For example, if I have a 'people' table and a 'dept' table with a linked field on ID number and connect them with an inner join in a view, you have to use the field in the 'people' table in the WHERE clause, not a field in the 'dept' table. If I write the query:

select * from view_statement where deptName = 'Maintenance'

It will return all the records that have 'Maintenance' in the child field and also any that had null values in the field.

If I write the query:

select * from view_statment where parent_dept_ID = (Select ID from dept where deptName = 'Maintenance')

I get only the records from the Maintenance Department and no nulls.

If anyone has any thoughts on this, it would be nice to know how to keep my queries short, but if not I will just write my queries a little longer and get the right data I suppose.

Sincerely,

Scott