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

Any suggestions would be highly appreciated.

Wesam

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

Use a SQL Statement with the IN Operator

... WHERE Field NOT IN (SELECT Field FROM...

or use the EXISTS Command

... WHERE NOT EXISTS(SELECT Field FROM ...

see JetSql35.hlp for Examples


Hope that helps
Etoscha

[Wesam]

Thank you very much for this tip, it worked perfectly.

wEsAm

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

[bashfirst]

Although the Not In or Not Exists clause will do the trick, it is not very efficient since each record has to be compared to the entire list. A better way to do this type of query is to perform an outer join and then look for Nulls.

SELECT Table1.Field1
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE (((Table3.Field1) Is Null))

You may want to try this, particularly if your table size is large.

Bash

[Wesam]

Hmmm .. pretty fast, I've just tried it. Thank you for your tip it's great.

Wesam

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