syntax problem with query

[Rich]

>>
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.
<<

Here's the query
select count(fld1) from
(select fld1 from tbl1 where fld1 not in (select * from tbl2) or fld1 is null)

Based on the data below, if I run just

select fld1 from tbl1 where fld1 not in (select * from tbl2) or fld1 is null

this returns 7 records. Since tsql doesn't count null values I want to
retrieve the count of records from Select fld1 from tbl1 ...

How can I retrieve this count of 7 records which includes the null values?

create table tbl1(
fld1 varchar(100)
)

insert into tbl1 values('Gurney Van')
insert into tbl1 values('Immediate')
insert into tbl1 values('Repatriation')
insert into tbl1 values('Scheduled - 4 or more hrs.')
insert into tbl1 values('Scheduled - Less than 4 hrs.')
insert into tbl1 values('Stat Urgent')
insert into tbl1 values('Team Transport')
insert into tbl1 values('Urgent')
insert into tbl1 values('Wheelchair')
insert into tbl1 values(null)
insert into tbl1 values(null)
insert into tbl1 values(null)
insert into tbl1 values('tim')
insert into tbl1 values('tim')
insert into tbl1 values('tim')
insert into tbl1 values('tim')

create table tbl2(
fld1 varchar(100)
)

insert into tbl2 values('Gurney Van')
insert into tbl2 values('Immediate')
insert into tbl2 values('Repatriation')
insert into tbl2 values('Scheduled - 4 or more hrs.')
insert into tbl2 values('Scheduled - Less than 4 hrs.')
insert into tbl2 values('Stat Urgent')
insert into tbl2 values('Team Transport')
insert into tbl2 values('Urgent')
insert into tbl2 values('Wheelchair')

Thanks,
Rich

[Rich]

I fixed the syntax part: I changed the query as follows

select count(t1.fld1) from

(select fld1 from tbl1 where fld1 not in (select * from tbl2) or fld1 is

null) t1

But is there a way to get a count of records where the value is null?

[Rich]

OK. This seems to do the trick:

select count(*) from

(select fld1 from tbl1 where fld1 not in (select * from tbl2) or fld1 is
null) t1

[oj]

These should do:

e.g.
--#1
select count(*) cnt1
from tbl1
where not exists(select * from tbl2 where tbl2.fld1=tbl1.fld1)

--#2
select count(*) cnt2
from tbl1 left join tbl2 on tbl1.fld1=tbl2.fld1
where tbl2.fld1 is null

--
-oj

"Rich" <Ri...@discussions.microsoft.com> wrote in message
news:971279CD-A61E-4B8D...@microsoft.com...

[Aaron Bertrand [SQL Server MVP]]

Right, because if you are counting specific values, NULL values don't count
(no pun intended). Your use of * negates this side effect.

"Rich" <Ri...@discussions.microsoft.com> wrote in message

news:812631E7-24F7-480E...@microsoft.com...

[Rich]

Thanks all for your replies and additional suggestions.

Rich