Solved

Checking where a value is not in another?

Posted on 2009-05-18
3
181 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 500 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 92

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now