return rows that contain no null values

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)

return rows that contain no null values

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-01-23 : 17:21:21

quote:

Column A Column B Column C Column D
1 6 10 15
1 6 NULL 15
1 6 NULL NULL
1 7 10 16
1 7 NULL 16
1 7 NULL NULL
1 NULL NULL NULL
2 8 11 17
2 8 NULL 17
2 8 NULL NULL
2 NULL NULL NULL
3 9 12 18
3 9 NULL 18
3 9 NULL NULL
3 NULL NULL NULL
4 6 13 19
4 6 NULL 19
4 6 NULL NULL
4 NULL NULL NULL
5 8 14 20
5 8 NULL 20
5 8 NULL NULL
5 NULL NULL NULL
NULL NULL NULL NULL

I have the above table. What I'd like to do is filter out all the rows that have at least one null value. Essentially, I'd be left with all rows that contain only data (in this case, numbers). How may I go about accomplishing this task?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-23 : 17:25:39

Here is one way:

DECLARE @Yak TABLE (ColA INT, ColB INT, ColC INT, ColD INT)

INSERT @Yak
SELECT 1, 6, 10, 15
UNION ALL SELECT 1, 6, NULL, 15
UNION ALL SELECT 1, 6, NULL, NULL
UNION ALL SELECT 1, 7, 10, 16
UNION ALL SELECT 1, 7, NULL, 16
UNION ALL SELECT 1, 7, NULL, NULL
UNION ALL SELECT 1, NULL, NULL, NULL
UNION ALL SELECT 2, 8, 11, 17
UNION ALL SELECT 2, 8, NULL, 17
UNION ALL SELECT 2, 8, NULL, NULL
UNION ALL SELECT 2, NULL, NULL, NULL
UNION ALL SELECT 3, 9, 12, 18
UNION ALL SELECT 3, 9, NULL, 18
UNION ALL SELECT 3, 9, NULL, NULL
UNION ALL SELECT 3, NULL, NULL, NULL
UNION ALL SELECT 4, 6, 13, 19
UNION ALL SELECT 4, 6, NULL, 19
UNION ALL SELECT 4, 6, NULL, NULL
UNION ALL SELECT 4, NULL, NULL, NULL
UNION ALL SELECT 5, 8, 14, 20
UNION ALL SELECT 5, 8, NULL, 20
UNION ALL SELECT 5, 8, NULL, NULL
UNION ALL SELECT 5, NULL, NULL, NULL
UNION ALL SELECT NULL, NULL, NULL, NULL

SELECT *
FROM @Yak
WHERE 
	ColA IS NOT NULL
	AND ColB IS NOT NULL
	AND ColC IS NOT NULL
	AND ColD IS NOT NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-23 : 17:26:56

Here is another (no better but different:

select * from myTable ColA=ColA AND ColB=ColB AND ColC=ColC [AND ...]

Be One with the Optimizer
TG

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-23 : 17:27:41

WHERE ([Column A] IS NULL OR [Column B] IS NULL OR [Column C] IS NULL OR [Column D] IS NULL)

hey

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-24 : 01:52:53

One improper way

WHERE 
	ColA >''
	AND ColB >''
	AND ColC >''
	AND ColD >''

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-24 : 01:54:52

quote:
Originally posted by hey001us

WHERE ([Column A] IS NULL OR [Column B] IS NULL OR [Column C] IS NULL OR [Column D] IS NULL)

hey

Opposite of what was asked

Madhivanan

Failing to plan is Planning to fail

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-24 : 11:15:04

quote:

Originally posted by Lamprey

Here is one way:

DECLARE @Yak TABLE (ColA INT, ColB INT, ColC INT, ColD INT)

INSERT @Yak
SELECT 1, 6, 10, 15
UNION ALL SELECT 1, 6, NULL, 15
UNION ALL SELECT 1, 6, NULL, NULL
UNION ALL SELECT 1, 7, 10, 16
UNION ALL SELECT 1, 7, NULL, 16
UNION ALL SELECT 1, 7, NULL, NULL
UNION ALL SELECT 1, NULL, NULL, NULL
UNION ALL SELECT 2, 8, 11, 17
UNION ALL SELECT 2, 8, NULL, 17
UNION ALL SELECT 2, 8, NULL, NULL
UNION ALL SELECT 2, NULL, NULL, NULL
UNION ALL SELECT 3, 9, 12, 18
UNION ALL SELECT 3, 9, NULL, 18
UNION ALL SELECT 3, 9, NULL, NULL
UNION ALL SELECT 3, NULL, NULL, NULL
UNION ALL SELECT 4, 6, 13, 19
UNION ALL SELECT 4, 6, NULL, 19
UNION ALL SELECT 4, 6, NULL, NULL
UNION ALL SELECT 4, NULL, NULL, NULL
UNION ALL SELECT 5, 8, 14, 20
UNION ALL SELECT 5, 8, NULL, 20
UNION ALL SELECT 5, 8, NULL, NULL
UNION ALL SELECT 5, NULL, NULL, NULL
UNION ALL SELECT NULL, NULL, NULL, NULL

SELECT *
FROM @Yak
WHERE 
	ColA IS NOT NULL
	AND ColB IS NOT NULL
	AND ColC IS NOT NULL
	AND ColD IS NOT NULL

Here is another approach:


	select * from @t
        where (colA + colB + ColC + ColD) is not null

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-24 : 12:51:54

quote:
Here is another approach:

select * from @t
where (colA + colB + ColC + ColD) is not null

The datatypes have to "play well togeter" for this approach.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources