Question about my query ??? is it right ??

[Wesam]

Hi,

I have a problem in writing my query which extracts some records from a table which are (the records) not existing in another table.

E.g.: I have the following tables:

'Table1' with one field 'Index'
'Table2' with one field 'Index'

Table1.Index Table2.Index
------------ ------------
3 4
2 5
0 12
1 17
22 0
13 1

my question is how to write a query that compares all records in Table1.Index with all records in Table2.Index and extracts all those which are not matching each other. In above example the result should be:

3
2
22
13

.. my suggestion was:

SqlCommand='SELECT DISTINCTROW [Table1].Index
FROM [Table1] INNER JOIN [Table2] ON [Table1].Index <> [Table2].Index'

.. but it doesn't work, however, if change the criterion to select the opposite set it works fine !!

SqlCommand='SELECT DISTINCTROW [Table1].Index
FROM [Table1] INNER JOIN [Table2] ON [Table1].Index = [Table2].Index'

.. could you help me please

I appreciate your assistance

Wesam

------------------

[JohnAtWork]

Hmm this is using Access specific SQL so the join works a little bit differently than you are familiar with.

The meat of the matter, however, is in the WHERE clause at the end...

SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.Index = Table2.Index
WHERE ((Table2.Index) Is Null);

You would want to use an outer join, methinks. Sorry, I don't know the ANSI SQL for it. . .

------------------
Legalese:
Anything I say on this forum is strictly my own opinion, and if I make a jerk out of myself, it has NOTHING to do with the company.

[Wesam]

Unfortunately, I tried your code but it didn't work; the result query is always empty (no records), however, I tried RIGHT/LEFT JOINs but none of them gave me the result I want. Have you got any idea !!!
Thank you for your help

Wesam

------------------