I have 3 tables all with the data field (field1). I want to extract a
recordset with distinct values from these tables BUT I do not want
null or blanks to appear in the recordset. The following is what I am
using (failing with).

SELECT DISTINCT field1 FROM DB1
UNION
SELECT DISTINCT field1 FROM DB2
UNION
SELECT DISTINCT field1 FROM DB3 WHERE field1 != "(null)" AND field1 !
=""

If I drop the WHERE clause, the select works but, of course, I get
nulls.l

Appreciate suggestions on this one.

Thank you..

Bruce

On 2011-01-14 22:09, bruce wrote:
I'm not clear on whether you want to avoid null or '(null)' but I'll
assume null. Null equals nothing, not even null so you cannot use for
example = to test for null. There is a special predicate IS [NOT] NULL
that can be used to compare against null:

SELECT DISTINCT field1 FROM DB3
WHERE field1 IS NOT NULL
AND field1 !=""

FYI, != is not standard even though many dbms accepts it, you can use <>
instead. Also ' is the standard string delimiter, and finally union will
itself remove duplicates, so there is no point in using distinct:

SELECT field1 FROM DB1
UNION
SELECT field1 FROM DB2
UNION
SELECT field1 FROM DB3
WHERE field1 IS NOT NULL
AND field1 <> ''

or

SELECT field1 FROM DB1
UNION
SELECT field1 FROM DB2
UNION
SELECT field1 FROM DB3
WHERE coalesce(field1, '') <> ''

/Lennart

On Jan 14, 5:13*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thank you very much. Your changes work like a charm. I've changed !=
to <>, I've dropped DISTINCT, and changed the test for null to "IS NOT
NULL". Also, changed my quote to a single quote mark.

Thanks again...

Bruce