SQL 2008 query: return results where row not in another table

5 pts.
Tags:
Query
SQL 2008
SQL Query
Subquery
hi,

running sql2008.  i have the following two temp tables.  the first is just all web service errors for a date range.  the second is simply 20 rows that contain values of types of web service errors.

--temp tbl #1 select [date],[err],[srv],[e1],[e2] into #tblExcept from netlog where [err] like '%web service%' and convert(char(10),[date],120) >= @startDate and convert(char(10),[date],120) <= @endDate order by [date]

--temp tbl #2 create table #tblWSerr (errName nvarchar(255)); insert into #tblWSerr values ('Error connecting'),                              ('server down'),                              ('mainframe'),                              ('invalid call'), . . . .                              ('request aborted');

i need to execute 3 queries:  1) return a count for each occurrence of records in tbl #2 as in tbl1.  2) do the opposite of query #1, and 3) count total number of records returns by #2.

1)the first is below which returns a count of each web service error in tbl #1 and that works perfectly:

select t2.errName as 'Err Msg',count(t1.[message]) as 'Count' from #tblExcept t1,#tblWSerr t2 where t1.[message] like '%' + t2.errName + '%' group by t2.errName

2) have tried many different ways by using other operators and not exists, in, joins, or just flipping the above query by using "not like" (see below), but results are of course wrong:

select t1.[Message],t2.errName as 'Err Msg',count(t1.[message]) as 'Count' from #tblWebSvcExcept t1,#tblWSerr t2 where t1.[message] not like '%' + t2.errName + '% group by t1.[message]

questions: what query can i use to return a count of all occurences NOT in tbl2 (opposite of #1)?

thanks in advance.

 



Software/Hardware used:
sql2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 + ‘%’)
)
</pre>

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following