select statement - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select statement

Author

Topic

rbohaty
Yak Posting Veteran

71 Posts

Posted - 2003-05-13 : 21:34:09

I have 3 fields - field1 field2 field3, I want to create a query that will return the following info:

If one of the fields is not null and either 1 or both of the other fields are null.

either of the fololwoing scenerios would return in a query

Field1 = data
Field2 = null
Field3 = null

or

Field1 = null
Field2 = data
Field3 = data

The following would not:

Field1 = null
Field2 = null
Field3 = null

or

Field1 = data
Field2 = data
Field3 = data

Thanx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-13 : 21:52:12

SELECT * FROM myTable
WHERE (Field1 IS NULL OR Field2 IS NULL OR FIELD3 IS NULL)
AND COALESCE(Field1, Field2, Field3) IS NOT NULL

rbohaty
Yak Posting Veteran

71 Posts

Posted - 2003-05-13 : 22:40:42

Here is my where clause:

How do I segragate the fields I want to evaluate as null from the main criteria? I have thw 2 fields that are equal to statements and the others (userdef17, uzprofile, uzbanking) are the fields I want to evaluate for nulls...

WHERE (dbo.CONTACT1.KEY4 = 'College' AND dbo.CONTACT2.USERDEF07 = 'Active' AND dbo.CONTACT2.USERDEF17 IS NOT NULL AND dbo.CONTACT2.UZBANKING IS NULL AND dbo.CONTACT2.UZPROFILE IS NULL) AND COALESCE(dbo.CONTACT2.USERDEF17, dbo.CONTACT2.UZBANKING, dbo.CONTACT2.UZPROFILE) IS NOT NULL

rbohaty
Yak Posting Veteran

71 Posts

Posted - 2003-05-13 : 22:44:11

I got it, it just took a well placed (.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources