Resolved Access query Not in another query

[JuggaloBrotha]

I have a query that matches two tables which is working fine and I need to have another query that grabs everything from a table where the record's aren't matched.

Here's the matching query which is working fine:

SELECT SourceFiles.FileName
FROM SourceFiles INNER JOIN DestFiles ON (SourceFiles.FileName=DestFiles.FileName) AND (SourceFiles.FileName=DestFiles.FileName) AND (SourceFiles.FileSize=DestFiles.FileSize) AND (SourceFiles.Hash=DestFiles.Hash);

And here's the query that I'm just not getting quite right (I either get a "this sub query can only ever return 1 record" or it runs and there are 0 records returned) by looking at the two tables, I'm supposed to be getting 3 records right now.

'This one returns 0 records:
SELECT SourceFiles.FileName, SourceFiles.FileSize, SourceFiles.Hash
FROM SourceFiles
WHERE NOT EXISTS (SELECT * FROM Matches);

'This one gives me the error of it only ever returning 1 record:
SELECT SourceFiles.FileName, SourceFiles.FileSize, SourceFiles.Hash
FROM SourceFiles
WHERE NOT (SELECT * FROM Matches);

I just don't know what I'm doing wrong here, any suggestions?

 

[Tom]

Look at your subquery, your returning all records in the table... Also what is your comparison field in the Where clause?

[COLOR="Blue"]Select[/COLOR]
    sf.FileName, 
    sf.FileSize, 
    sf.Hash
[COLOR="blue"]From [/COLOR]SourceFiles [COLOR="blue"]As [/COLOR]sf
[COLOR="blue"]Where [/COLOR]sf.FileName [COLOR="Gray"]Not In[/COLOR] ([COLOR="blue"]Select [/COLOR]mt.FileName [COLOR="blue"]From [/COLOR]Matches [COLOR="blue"]As [/COLOR]mt)

There are additional ways to get the same results using joins

 

[JuggaloBrotha]

And it's not until I actually get a proper nights sleep where I realize that I was going about this the completely wrong way:

SELECT sf.FileName, sf.FileSize, sf.Hash
FROM SourceFiles AS sf INNER JOIN DestFiles AS df ON sf.FileName = df.FileName
WHERE sf.FileSize <> df.FileSize OR sf.Hash <> df.Hash;

I don't want the inverse of the records where all three match, I only want the records where the FileName column matches in both table and either the Hash for the FileSize columns are different. Talk about waking up and realizing how misguided my mind was yesterday.

 

[Tom]

Gottcha but what does that have to do with records in table Matches?

 

[JuggaloBrotha]

Gottcha but what does that have to do with records in table Matches?

It turns out it doesn't, once I made that new query I realized I don't even need to matches query so I'm not using it anymore. The rest of the app still uses it but I don't need it for this piece.