BorlandTalk.com

DA Brookes · Guest

Hi (sorry for the long post...)

Is there a correct method for using "null" in joined queries?

I have a query (two tables in different databases) that extracts information
fine if I provide (eg) con_ref = 'TH79006' and group_no = '002'

However if I provide (for a different contract)

con_ref = 'TH79009' and group_no is null

I do not get any output. There is matching data in the database, which
extracts fine as long as I don't join tables (see last example)

I'm using this method in an attempt to speed up a lookup field, and it
works a treat as long as I don't have a "null" entry.

SELECT SUM(DB1_m19c.ORIG_EST) AS c1a, DB1_m19c.CON_REF,
DB1_m19c.GROUP_no, DB1_m19c.WC_NO, DB2_wclist.WC_DESC
FROM M19c DB1_m19c INNER JOIN
ms_projdir.DBO.wclist DB2_wclist ON DB1_m19c.CON_REF =
DB2_wclist.CON_REF COLLATE SQL_Latin1_General_CP1_CI_AS AND
DB1_m19c.WC_NO = DB2_wclist.WC_NO COLLATE
SQL_Latin1_General_CP1_CI_AS AND
DB1_m19c.GROUP_no = DB2_wclist.group_no COLLATE
SQL_Latin1_General_CP1_CI_AS
WHERE (DB1_m19c.CON_REF = 'TH79006') AND (DB1_m19c.GROUP_no IS NULL)
GROUP BY DB1_m19c.CON_REF, DB1_m19c.GROUP_no, DB1_m19c.WC_NO,
DB2_wclist.WC_DESC

------------------
(This code produces results, but then I have to lookup the company name
seperately.)
SELECT SUM(ORIG_EST) AS c1a, CON_REF, GROUP_no, WC_NO
FROM M19c DB1_m19c
WHERE (CON_REF = 'TH79006') AND (GROUP_no IS NULL)
GROUP BY CON_REF, GROUP_no, WC_NO
-------------------

I'm obviously doing something wrong... Any ideas would be welcome
Regards
Dave

Vitali Kalinin · Guest

"DB1_m19c.GROUP_no = DB2_wclist.group_no"
comparison will give you Null but not True (as you probably expected) if
DB1_m19c.GROUP_no IS NULL since Null is not equal to another Null. Thus
resultset will be empty.

Regards,
Vitali

"DA Brookes" <david_dot_brookes_at_interserveprojects.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ
× ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: news:40865f75$1 (AT) newsgroups (DOT) borland.com...

DA Brookes · Guest

Thanks Vitali

I expected 'null' to be matched with 'null' in the same (successful) way as
'002' with '002' in each table.

I've adjusted my code accordingly. ie I've removed the line that joined by
group_no and amended the following line.

WHERE (DB1_m19c.CON_REF = 'TH79006') AND (DB2_wclist.group_no IS NULL)
AND (DB1_m19c.GROUP_no IS NULL)

Regards
Dave

"Vitali Kalinin" <vitkalinin (AT) yahoo (DOT) com> wrote