Finding Nulls from Query! - 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)

Finding Nulls from Query!

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-15 : 13:26:49

Hi,

I have two tables, table1 and table2. its a one to many relation from table1 to table2 but not always!

Every record in table1 with certain condition is supposed to have records in table2. Like say for example,

Table1 has fields: field1, field2, field3, field4. (field1 id PK)

Table2 has fields: field5, field1, field6, field7, field8,.... (field1 is foriegn key)

When field3 = 100 then table2 is supposed to have records with field1.
I want to show this as a exception report if there are no records for field1 when field3 = 100 in table2.

For this i did a left join on table2. So it gave me bunch of records with null values along with rest of the records. Now how can i just show only the null value records.

When i used the following query its giving be nothing(no records whatsoever)

select *
from table1
left join table2 on table1.field1 = table2.field1
where table1.field3 = 100 and table2.field6 = null

The same query if i change it like this,

select *
from table1
left join table2 on table1.field1 = table2.field1
where table1.field3 = 100

then i get.........
field1 field2 field3 field4 field5 field1 field6
abc xyz 100 xyz null null null
abc kjh 100 hjy null null null
syx kji 109 juh kii syx 8769
syx kjr 107 jeh kit syx 8659

Here i only want the first two records alone. here table2 should have records since table1.field3 = 100.

Hope sombody can help me out with this.
Thanks a lot in advance,
Nitu

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-15 : 13:34:39

I didn't bother to try to understand all the logic but:
<col> = null won't work.
Change it to:
<col> is null

Be One with the Optimizer
TG

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-15 : 13:40:10

Thanks TG,

That works.

--Nitu

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-16 : 02:24:27

Null Value cannot be equated(=)
for ex :
DECLARE @val CHAR(4)
SET @val = NULL
SET ANSI_NULLS ON
If @val = NULL
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

will return only false. Try
if @val is null in the above to get the required answer.

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources