Query on using SQL to exclude records

[mattygriff]

My problem is this:

I have a MS-Access97 database with 2 tables, one containing a list of sports FIXTURES and another containing the RESULTS of those fixtures. When the user comes to enter the latest result, I want to offer them only the fixtures which have already been played but which have not yet had a result entered.

I think the SQL query would need to obtain all records from the FIXTURES table with a fixture date equalling or prior to the current date (no problem there) but only if they DO NOT have an entry in the RESULTS table (the common field is FixID).

I think there is a SQL statement something like "NOT IN" but I have no idea how to formulate the SQL query.

Can anyone help ?

 

[sunaj]

Hi,

You can indeed use 'NOT IN', but bee very carefull, if your tables are large it will take forever...

SELECT * FROM FIXTURES WHERE FixID NOT IN (SELECT DISTINCT FixID FROM RESULTS)

..depending on how your fields are named.


Sunaj

 

[webspy]

try this query

select * from fixtures where fixid not in(select fixid from results)

with regards
webspy

 

[mattygriff]

Thanks Guys, but how would I structure my query to include the initial selection of fixtures based on date ?

Would it be &quot;SELECT * FROM fixtures WHERE (fixDate<=#&quot; & now() & &quot;# AND fixID NOT IN (SELECT DISTINCT fixID FROM results))&quot; ???

 

[sunaj]

yes.