Checking where a value is not in another?

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.
directxBOBAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
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
 
directxBOBAuthor Commented:
Ahhh, sorry Matthew, I was in the middle of accepting the above answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.