MS SQL filter list of records

[maxamis4]

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:

SELECT     dbo.call_req.ref_num
FROM         dbo.audit_log FULL OUTER JOIN
                      dbo.call_req ON dbo.audit_log.audobj_persid = dbo.call_req.persid
GROUP BY dbo.call_req.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
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 
                      (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

SELECT     ref_num
FROM         dbo.call_req
WHERE     (NOT EXISTS
                          (SELECT     call_req_1.ref_num
                            FROM          dbo.audit_log FULL OUTER JOIN
                                                   dbo.call_req AS call_req_1 ON dbo.audit_log.audobj_persid = call_req_1.persid
                            GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
                            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 
                                                   (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

[nbiswas]

Solution to MS SQL filter list of records

Try this

Solution1 : (With EXCEPT)

SELECT ref_num
FROM   dbo.call_req

[B]EXCEPT[/B]

SELECT call_req_1.ref_num
FROM   dbo.audit_log 
FULL OUTER JOIN dbo.call_req AS call_req_1 
ON dbo.audit_log.audobj_persid = call_req_1.persid
GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
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 (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved')))

Solution 2:(Where Not In)

SELECT ref_num
FROM   dbo.call_req 
[B]WHERE ref_num NOT IN[/B](
SELECT call_req_1.ref_num
FROM   dbo.audit_log 
FULL OUTER JOIN dbo.call_req AS call_req_1 
ON dbo.audit_log.audobj_persid = call_req_1.persid
GROUP BY call_req_1.ref_num, dbo.audit_log.attr_name, dbo.audit_log.attr_before_val, dbo.audit_log.attr_after_val
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 (NOT(dbo.audit_log.attr_after_val IN (N'Resolved', N'Closed', N'Externally Resolved'))))

I hope this should work.

[ck9663]

Be careful how you handle NULL values, specially with NOT EXISTS. Here, read this...

Happy Coding!!!

--- CK