By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

MS SQL filter list of records

maxamis4
Expert 100+
P: 295
Hello folks,

I am still moving into the world of sql server and I need some help. In oracle there is a statement that is called "in list". Basically means what ever is returned from a select statement is filtered. I am trying to rewrite something similar.

So here is the some definitions to help understand. My audit table has multiple records with the same reference ID. In my audit table I want to find specific types of records which are duplicated, and then I want to filter them out from another select statement which basically builds my "not in list" statement. It runs but returns not results. Can someone tell me if there is another way of doing this?

Here is my filter statement by itself:

Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.call_req.ref_num
  2. FROM         dbo.audit_log FULL OUTER JOIN
  3.                       dbo.call_req ON dbo.audit_log.audobj_persid = dbo.call_req.persid
  4. GROUP BY dbo.call_req.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  5. HAVING      (NOT (dbo.call_req.ref_num IS NULL)) AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) AND 
  6.                       (NOT (dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved')))
This returns all the proper records

This is when i try to use the statement above to exclude those records

Expand|Select|Wrap|Line Numbers
  1. SELECT     ref_num
  2. FROM         dbo.call_req
  3. WHERE     (NOT EXISTS
  4.                           (SELECT     call_req_1.ref_num
  5.                             FROM          dbo.audit_log FULL OUTER JOIN
  6.                                                    dbo.call_req AS call_req_1 ON dbo.audit_log.audobj_persid = call_req_1.persid
  7.                             GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  8.                             HAVING      (NOT (call_req_1.ref_num IS NULL)) AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) AND 
  9.                                                    (NOT (dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved')))))
This returns zero values which is not accurate. Any help or links appreciated
Dec 21 '09 #1

✓ answered by nbiswas

Try this

Solution1 : (With EXCEPT)

Expand|Select|Wrap|Line Numbers
  1. SELECT ref_num
  2. FROM   dbo.call_req
  3.  
  4. EXCEPT
  5.  
  6. SELECT call_req_1.ref_num
  7. FROM   dbo.audit_log 
  8. FULL OUTER JOIN dbo.call_req AS call_req_1 
  9. ON dbo.audit_log.audobj_persid = call_req_1.persid
  10. GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  11. HAVING(NOT (call_req_1.ref_num IS NULL))  
  12. AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) 
  13. AND (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved')))

Solution 2:(Where Not In)

Expand|Select|Wrap|Line Numbers
  1. SELECT ref_num
  2. FROM   dbo.call_req 
  3. WHERE ref_num NOT IN(
  4. SELECT call_req_1.ref_num
  5. FROM   dbo.audit_log 
  6. FULL OUTER JOIN dbo.call_req AS call_req_1 
  7. ON dbo.audit_log.audobj_persid = call_req_1.persid
  8. GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  9. HAVING(NOT (call_req_1.ref_num IS NULL))  
  10. AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) 
  11. AND (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved'))))
I hope this should work.

Share this Question
Share on Google+
2 Replies


nbiswas
100+
P: 149
Try this

Solution1 : (With EXCEPT)

Expand|Select|Wrap|Line Numbers
  1. SELECT ref_num
  2. FROM   dbo.call_req
  3.  
  4. EXCEPT
  5.  
  6. SELECT call_req_1.ref_num
  7. FROM   dbo.audit_log 
  8. FULL OUTER JOIN dbo.call_req AS call_req_1 
  9. ON dbo.audit_log.audobj_persid = call_req_1.persid
  10. GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  11. HAVING(NOT (call_req_1.ref_num IS NULL))  
  12. AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) 
  13. AND (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved')))

Solution 2:(Where Not In)

Expand|Select|Wrap|Line Numbers
  1. SELECT ref_num
  2. FROM   dbo.call_req 
  3. WHERE ref_num NOT IN(
  4. SELECT call_req_1.ref_num
  5. FROM   dbo.audit_log 
  6. FULL OUTER JOIN dbo.call_req AS call_req_1 
  7. ON dbo.audit_log.audobj_persid = call_req_1.persid
  8. GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
  9. HAVING(NOT (call_req_1.ref_num IS NULL))  
  10. AND (dbo.audit_log.attr_before_val IN (N'Resolved', N'Closed', N'Externally Resolved')) 
  11. AND (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved'))))
I hope this should work.
Dec 21 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Be careful how you handle NULL values, specially with NOT EXISTS. Here, read this...

Happy Coding!!!

--- CK
Dec 21 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.