Checking where a value is not in another?
Posted on 2009-05-18
I have two tables:
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.