Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Checking where a value is not in another?

Posted on 2009-05-18
3
Medium Priority
?
226 Views
Last Modified: 2012-05-07
I have two tables:

AuditTrail
Assets

AuditTrail is used to track the actions / changes made to assets. Within the audit trail I store full details of the asset in an INFO field where information is seperated by a |

Some sample data that is stored in a field of the auditrail:

L004|STL046|Other| 2008-10-14 | 2009-05-16

Account | Serial Number | Type | Added On | Altered On

I need to put a report together, that would isolate any assets have an audittrail but are not in the assets table. EG: corruption, somebody manually deleted a row from the table etc...

So in my above case I would like

select * from assets where serial-number = 'STL046'
select * from audittrail where info like 'STL046'

The problem is I am not sure how I would use "Not IN" to compare and see if there is an audit trail or an asset no longer in the table.
0
Comment
Question by:directxBOB
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 24410725
I am not getting your quetion ...can you clearity little more

Do you want something like this present in one table but not in another

select * from assets where serial-number = 'STL046'
select * from audittrail where info not in (select serial-number  from assets )

OR

select * from assets where serial-number not in ( select info from audittrail )
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24411046
Hello directxBOB,

NOT IN will work, but it is very inefficient.  This should give better performance:

SELECT at.*
FROM AuditTrail at LEFT JOIN
      Assets a ON at.SerialNumber = a.SerialNumber
WHERE a.SerialNumber IS NULL

Regards,

Patrick
0
 

Author Comment

by:directxBOB
ID: 24411190
Ahhh, sorry Matthew, I was in the middle of accepting the above answer.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question