Posted by Hiho on 01/23/08 09:16

Hi,

I have a query like this :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...

The problem is that I don't want to return the results where x3 is
NULL.

Writing :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ... AND x3 IS NOT NULL

doesn't work.

The only solution I found is to write :

SELECT * FROM
(
(SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...
) AS R1
)
WHERE R1.x3 IS NOT NULL

Is there a better solution? Can I use an EXISTS clause somewhere to
test if x3 is null without having to have a 3rd SELECT statement?
There's probably a very simple solution to do this, but I didn't find
it.

Thanks

• Next in forum: Help-VB.NET and SQL Server 2005
• Prev in forum: Re: NOT EXISTS using single table?
• Thread view: NULL values in a SELECT in another SELECT

[Reply to this message]