SQL 2008 query: return results where row not in another table
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
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
ASKED:
August 17, 2011 10:02 PM
UPDATED:
March 31, 2012 6:12 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
