differents results from the same query execution

[IceBock]

Hi

I am executing the following queryin Access:

Query #1)
SELECT Table2.* FROM Table2
WHERE (((Table2.matchColumn) Not In (select DISTINCT table1.matchColumn from
table1)));



The column matchColumn in table2 and table1 are not unique. What I try to
do is to remove rows from table2 if they are in table1 (on the match column).


The problem is that i get random results with my query. Some time, i got
1515 rows returned by the query, somes other times, i got 1514 or 1516 rows
returned.

By adding the "distinct" word before table2 (see query #2), i get stable
result on the number of returned rows (and the correct number of rows by the
way).
I am not sure tought I should use distinct before table2 as I only wish to
eliminate rows from table2 if they are present in table1.

So the question is whi Access return me random result from table1 and also
are the query 2 better than the one to do what i want ?


Query #2)
SELECT Table2.* FROM Table2
WHERE (((Table2.correspondance) Not In (select DISTINCT
table1.correspondance from table1)));



Thanks
Patrick Blanchette

 

[pietlinden]

Hi

I am executing the following queryin Access:

Query #1)
SELECT Table2.*  FROM Table2
WHERE (((Table2.matchColumn) Not In (select DISTINCT table1.matchColumn from
table1)));

The column matchColumn in table2 and table1 are not unique.  What I tryto
do is to remove rows from table2 if they are in table1 (on the match column).

The problem is that i get random results with my query.  Some time, i got
1515 rows returned by the query, somes other times, i got 1514 or 1516 rows
returned.

By adding the "distinct" word before table2 (see query #2), i get stable
result on the number of returned rows (and the correct number of rows by the
way).
I am not sure tought I should use distinct before table2 as I only wish to
eliminate rows from table2 if they are present in table1.

So the question is whi Access return me random result from table1 and also
are the query 2 better than the one to do what i want ?

Query #2)
SELECT Table2.*  FROM Table2
WHERE (((Table2.correspondance) Not In (select DISTINCT
table1.correspondance from table1)));

Thanks
Patrick Blanchette

the standard way to do this is an outer join. Why do you even need
DISTINCT?

SELECT *
FROM Table2 LEFT JOIN Table1 ON
Table1.Correspondence=Table2.Correspondence
WHERE Table1.Correspondence IS NULL;

 

[IceBock]

Hi.
Thanks for the answer to the question, your query is better than mine and
give proper results. I will use my query this way in future ...

But I just not enderstand whi the first query I writen was executing with
random results ...

For the distinct, that was just a solution I tryed.