"Not Exists" problem

[barry_kellett99]

I have two tables, which both have a field called FixtureID in it. One table is called tbl_fixtures and the other tbl_reports.

I want to return a list of FixtureID's that are in tbl_fixtures, that are not in tbl_reports.
Simple? Sounds it!

I tried to use the Not Exists structure as follows but it returns No rows when it should return about a dozen or more.

SELECT fix.FixtureID
FROM tbl_Fixtures AS fix
WHERE NOT EXISTS (SELECT Rep.FixtureID FROM tbl_Reports AS Rep)

Then Access has reformatted it to the following:

SELECT fix.FixtureID
FROM tbl_Fixtures AS fix
WHERE (((Exists (SELECT Rep.FixtureID FROM tbl_Reports AS Rep))=False));

Which also makes sense logically to me, but again it is not bringing any rows back.

Lost and confused as to why it isnt working,
BK

 

[barry_kellett99]

Erm, After posting this i can see straight away what is wrong. I had the two queries back to front

SELECT Rep.FixtureID
FROM tbl_Reports AS Rep
WHERE (Exists (SELECT  fix.FixtureID FROM tbl_Fixtures AS fix)=true);

Works

Oh well!

 

[EMP]

SELECT Rep.FixtureID
FROM tbl_Reports AS Rep
WHERE (Exists (SELECT  fix.FixtureID FROM tbl_Fixtures AS fix)=true);

I believe your query returned every record from the tbl_Reports table because the subquery will always be true when there are records in the tbl_Fixtures table.

I want to return a list of FixtureID's that are in tbl_fixtures, that are not in tbl_reports.

Try the Find Unmatched Query Wizard, which can build a more efficient query than Not Exists. (To use Not Exists correctly, you need to use it in a correlated way, which is inefficient.)

^