question

RdS avatar image
RdS asked

sql2008: 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] 3) total count of rows errors from #2 questions: what query can i use to return a count of all occurences of tbl2 NOT in tbl1 (opposite of #1)? what query can i use to get total count of #2 thanks in advance.
sql-server-2008sub-query
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

y'know, this stuff looks suspiciously like another question asked a few weeks ago. I suspect it's homework time...
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
For the second query you have to use a **`LEFT JOIN`** and make a where condition to return rows where there is no corresponding record in the `#tblWSerr` - it means the errName will be `NULL` select t1.[Message] as 'Err Msg',count(t1.[message]) as 'Count' from #tblWebSvcExcept t1 LEFT JOIN #tblWSerr t2 ON t1.[message] like '%' + t2.errName + '%' WHERE t2.errName IS NULL group by t1.[message] For the 3) you have 2 possibilities: 1. Use SELECT SUM.. fro the query #2 2. Use the **`ROLLUP`** clause in GROUP BY and have the total included in the second query. I suggest to use the the second: select t1.[Message] as 'Err Msg',count(t1.[message]) as 'Count' from #tblWebSvcExcept t1 LEFT JOIN #tblWSerr t2 ON t1.[message] like '%' + t2.errName + '%' WHERE t2.errName IS NULL group by ROLLUP(t1.[message]) In the case the last line will contain `NULL` in the `Message` column and will represent a total count of errors.
14 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"have to use a `LEFT JOIN`"? There's usually more than one way to skin the cat in SQL. First off, how about a `RIGHT OUTER JOIN` ;)
0 Likes 0 ·
...also you've got syntax errors: one of the wilcards is missing a closing quote, the `GROUP BY` clause needs to include `t2.errName` and won't `t2.errName` in the `SELECT` clause always be `NULL`?
0 Likes 0 ·
Maybe I could write "should" instead of "have to", but if we use the original query and keep the `#tblWebSvcExcept` immediately after FROM, then we have to use LEFT JOIN. For RIGHT OUTER JOIN we need to switch the tables. Of course, there is a lot of possibilities and it's possible to write it even FULL OUTER JOIN with appropriate WHERE clause or even with INNER JOIN to Sub Query, which will filter out the record using eg. INTERSECT or EXCEPT set operators or we can ue the EXISTS in the WHERE condition. But why to make the things complicated?
0 Likes 0 ·
Related to the wrong systax.. I've just copied the conditions from the example provided and used them to show right wayt how to achieve results.... Going to fix this and remove the t2.errName from the original query as it will be always NULL and is not necessary in the query.
0 Likes 0 ·
"Should" still sounds prescriptive. May I suggest "could"?
0 Likes 0 ·
Show more comments
JamieC avatar image
JamieC answered
For your query number 2, am I correct in thinking (from the wording of the question) that you want to return the rows from table `#tblExcept` that do not appear in `#tblWebSvcExcept`? (You first query uses `#tblExcep`, confusingly.) If they do not appear, then the associated count will surely always be zero (is is it one, perhaps?) e.g. SELECT t2.errName AS "Err Msg", 0 AS "Count" FROM #tblWSerr t2 WHERE NOT EXISTS ( SELECT * FROM #tblWebSvcExcept t1 WHERE t1.[message] LIKE '%' + t2.errName + '%' ); ...or following your own attempt (thanks @Pavel Pawlowsk ;), something more like this: SELECT t1."message", NULL AS "Err Msg", COUNT(*) AS "Count" FROM #tblWebSvcExcept t1 WHERE NOT EXISTS ( SELECT * FROM #tblWSerr t2 WHERE t1."message" LIKE '%' + t2.errName + '%' ) GROUP BY t1."message";
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think, @RdS wants count of t1.message for which doesn't exists record in the #tblWSerr based on the condition @RdS wrote originally (NOT LIKE ....)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.