Use the Exists and Not Exists predicates – these are designed for just these kinds of queries. Because the table you want to retrieve has a “like” string compare, it is a little trickier:
<pre>select … from #tblExcept te1
where not exists (select 1 from #tblExcept te2, #tblWSErrs twe
where — first make sure we are using the SAME record from te2
te2.date = te1.date and te2.message = te1.message
and te2.err = te1.err and te2.srv = te1.srv
and te2.e1 = te1.e1 and te2.e2 = te1.e2
— now do the like compare
and te2.message like (‘%’ + twe.errName + ‘%’)
This could be made a lot simpler by adding a primary key column – just a simple record id, which could be an “Identity” column. Then the check to make sure we are looking at the same record would just be “… te2.pkcolumn = te1.pkcolumn …”.
Another comment – you are doing date conversions in your Where clause to convert the datetime value in the database to match what looks like an input string parameter. Performing conversions in a Where clause is highly inefficient – generally it means that the query optimizer is unable to use any indexes on the columns involved in the conversions, so it winds up doing a full table scan on the source table. It would be much more efficient to convert the input parameter values to datetime values and then use those in the where clause. Or, if that isn’t feasible, at least do the conversion on the parameters rather than on the database table columns.