Solved

Checking where a value is not in another?

Posted on 2009-05-18
3
173 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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 video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

11 Experts available now in Live!

Get 1:1 Help Now