Whats wrong with this query?

[ywenz]

SELECT *
FROM ImageProperties, OccupantDetails
WHERE ImageProperties.OccupantID <> OccupantDetails.ID;

ImageProperties keeps information about what type of occupant is shown in the image. OccupantID is a foreign key and OccupantID.ID is the primary key in the OccupantDetails table. This query is suppose to check for any invalid entries in ImageProperties (where OccupantID is an invalid ID). But the result of this query seems like an infinite loop, where the 1st record in ImageProperties is return many many times. Am I using &quot;<>&quot; wrong? Thanks

 

[mpastore]

Use this:

SELECT *
FROM ImageProperties
WHERE OccupantID not in(select ID from OccupantDetails)


Mike Pastore

Hats off to (Roy) Harper

 

[ywenz]

So now I'm running this query:

SELECT *
FROM ImageProps, VehicleDetails, Environment, DayOrNight, Motion, Weather, SSPos
WHERE ImageProps.OccupantID=9999 OR (ImageProps.VehicleID NOT IN(SELECT ID FROM VehicleDetails.ID)) OR ImageProps.EnvironmentID NOT IN(SELECT ID FROM Environment) OR ImageProps.DayOrNightID NOT IN(SELECT ID FROM DayOrNight) OR ImageProps.MotionID NOT IN(SELECT ID FROM Motion) OR ImageProps.WeatherID NOT IN(SELECT ID FROM Weather) OR ImageProps.SunshieldPosID NOT IN(SELECT ID FROM SSPos);

But it says:
&quot;Couldn't find file 'C:\Documents and Settings\ywenz\My Documents\VehicleDetails.mdb&quot;

Why does it think &quot;VehicleDetails&quot; is a database? VehicleDetails is a table in my DB. I'm really confused.