not-equal, theta join

[Guest]

I am trying to make a “not-equal†theta join between two tables. Table1 has
an id field that matches to an id field of Table2. I want to make a query
that will return all of the id values that are in Table1 but not used in
Table2. I started by creating a left join between the two tables (where all
records from Table1 are present and only matching records from Table2 are
present). I then added all of the fields from Table1 and for the criteria of
the id field I entered “<>Table2.IDâ€. When I run the query there are no
records returned. I cannot figure out why I’m not getting 44 records. I’ve
ran it with no criteria and fifty records were returned (the number of
records in Table1). I’ve also ran it with “table2.id†as my criteria and six
records were returned (the number of records in Table2).

Here is my Access generated SQL statement:

SELECT AN.AN_HEX_ID, AN.AN_DEC_ID, AN.Board, AN.ShippingDate
FROM AN LEFT JOIN Orders ON AN.AN_HEX_ID = Orders.AN_ID_HEX
WHERE (((AN.AN_HEX_ID)<>[orders].[an_id_hex]));

 

[Van T. Dinh]

Try:

SELECT AN.AN_HEX_ID, AN.AN_DEC_ID, AN.Board, AN.ShippingDate
FROM AN LEFT JOIN Orders ON AN.AN_HEX_ID = Orders.AN_ID_HEX
WHERE ([orders].[an_id_hex] Is Null);