<div>
Hello directxBOB,<br />
<br />
NOT IN will work, but it is very inefficient. &nbsp;This should give better performance:<br />
<br />
SELECT at.*<br />
FROM AuditTrail at LEFT JOIN<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Assets a ON at.SerialNumber = a.SerialNumber<br />
WHERE a.SerialNumber IS NULL<br />
<br />
Regards,<br />
<br />
Patrick
</div>


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

<div>
Ahhh, sorry Matthew, I was in the middle of accepting the above answer. 
</div>


Ahhh, sorry Matthew, I was in the middle of accepting the above answer.

<div>
<div class="content wysiwyg-content">
I have two tables:<br />
<br />
AuditTrail<br />
Assets<br />
<br />
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 |<br />
<br />
Some sample data that is stored in a field of the auditrail:<br />
<br />
L004|STL046|Other| 2008-10-14 | 2009-05-16<br />
<br />
Account | Serial Number | Type | Added On | Altered On<br />
<br />
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...<br />
<br />
So in my above case I would like<br />
<br />
select * from assets where serial-number = 'STL046'<br />
select * from audittrail where info like 'STL046'<br />
<br />
The problem is I am not sure how I would use &quot;Not IN&quot; to compare and see if there is an audit trail or an asset no longer in the table.
</div>
</div>


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.

Checking where a value is not in another?

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.