The problem that faces me relates to the statement below:

select (isnull(c.FIRSTNAMES,'')
        + ' '
        + isnull(c.[NAME],'')) ClientName,
    c.REFER ClientRef,
    p.CONT_NUM PolicyRef,
    p.POL_NUM PolicyID,
    cs.[Desc] ,
    p.STATUS

from S_POLMAI p
inner join S_CLIENT c
    on p.CLIENT_NUM = c.CLIENT_NUM
inner join Hydra.dbo.def_PolicyStatus cs
    on p.STATUS = cs.[ID]

where p.PLAN_CODE = 'DGT'
      and p.POL_NUM not in (select [id] from #temp1) -- Line in dispute
    and p.POL_NUM not in (select distinct PolicyID
                from TrackItems
                where [Description] like '%blar blar blar%'
                    or [Description] like '%blar blar blar%')
    and p.STATUS in (19,20)
and p.POL_NUM = ***** -- Test Case

order by c.[NAME]

With the 'Line in dispute' commented out the statement returns a single row relating to the POL_NUM entered as a test case. The table #temp1 contains a list of POL_NUM's excluding the one I have used as my test case. POL_NUM and [id] (returned by #temp1) are of the same data type. Running the full statement returns no rows. Changing the 'Line in dispute' to 'in' rather than 'not in', also returns no rows. Replacing the select statement with a comma delimited list of numbers excluding the test case POL_NUM, works as expected.

I've managed to reduce the problem to this statement.

select *
from S_POLMAI
where POL_NUM not in (select distinct [id] from #temp1)

There are over 2000 distinct POL_NUM's in S_POLMAI, exactly 180 distinct [id]'s in #Temp1

When I run the query above, 0 rows are returned. When take 'not' out of the where clause 179 rows are returned.

Problem solved. There was a null column in #Temp1

there is no need to use distinct in this query

select *
from S_POLMAI
where POL_NUM not in (select distinct [id] from #temp1)

atul