Problem with subquery

[betheball]

The following query works properly:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices

so does this one:

SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'

Each brings back a list of IDs. What I need is a list of all ids in the first recordset that do NOT appear in the second recordset. I would think that could be gotten by this:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

It does not error, but does not bring back any records when it should.

Thoughts?

[SQL_Hell]

hi


They are different queries with diferent criteria, so I would assume there is no data that matches the criteria in the second query.
Without knowing your data or your table structures its a bit difficult for anyone apart from you to be able to say why that query returns no records.

[Pauldohert]

try using an outer join all records form the first query and the criteria null in the second