Query that finds displays records items with missing data

[Harr777]

I'm trying to produce a query the will display a list of records of items that do not have a certain value in a connected table. The SQL Code should be something like:

SELECT tblAssets.SerialNum, tblMeterReads.ReadPeriod
FROM tblMeterReads INNER JOIN tblAssets ON tblMeterReads.SerialNumber = tblAssets.SerialNum
WHERE ((???????????????????????));

The WHERE part, I supose, should specify that the it IS NOT found in the tbleMeterReads. For expample, a item from the tblAssets that does not a have a record in the tbleMeterReads under the corresponding SerialNumber and the ReadPeriod "200404".

This is a bit of a challenge, because usually queries select data that exists in tables. This is trying to find records that do not exist.

Any help would be greatly appreciated.

 

[r937]

select tblAssets.SerialNum
  from tblMeterReads 
left outer
  join tblAssets 
    on tblMeterReads.SerialNumber 
     = tblAssets.SerialNum
   and tblAssets.ReadPeriod = '200404'
 where tblAssets.SerialNum is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[r937]

oops

select [b]tblMeterReads.ReadPeriod[/b]
  from tblMeterReads 
left outer
  join tblAssets 
    on tblMeterReads.SerialNumber 
     = tblAssets.SerialNum
   and tblAssets.ReadPeriod = '200404'
 where tblAssets.SerialNum is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Harr777]

Excellent, thanks a bunch!