SQL Question: Exclusive Entries comparing two tables

[waldemar]

I guess this must be a simple one, but I can't figure it out...

TABLE A and B have the same Key Index and usually they are linked 1:1 by this Index.

I want to check with one SQL Query, Entries that are only TABLE A but not in TABLE B (and vice versa)...

I read about an SQL Statement MINUS, but that one does not seem to exist in Jet SQL...? Anybody an idea?

 

[TrojanRabbit]

OUTER JOIN will show you that. In one query, A LEFT OUTER JOIN B ON ...; in another query, A RIGHT OUTER JOIN B ON ... or B LEFT OUTER JOIN A ON .... Include the key index from both tables, and it will show you the matches and misses. If you want, you can even specify that the missing index numbers be null. (WHERE <missing index> IS NULL) This will show only what's missing.

 

[waldemar]

I tried

SELECT *
FROM tblA LEFT OUTER JOIN tblB ON [tblB].[ID] = [tblA].[ID];

and it showed me the &quot;doubles&quot; - everything the tblB (tblB contains less than tblA), but I need the missing ones...

(this is called inter-thread-communication

 

[mikevh]

Sounds like you want the DISJUNCTION of the 2 tables;
anything that's in one or the other, but not both?
My idea would be basically the same as TrojanRabbit's.
If you have 2 tables, A and B, like this

Table A
ID field2
1 one
2 two
3 three
4 four
5 five
6 six

Table B
ID field2
5 five
6 six
7 seven
8 eight
9 nine
10 ten

Then the disjunction would be everything except the records
with keys 5 and 6, since those records occur in both tables.
The following will do this:

SELECT tableB.ID, tableB.field2 from tableB
LEFT JOIN tableA on tableB.ID = tableA.ID
WHERE tableA.ID IS NULL
UNION
SELECT tableA.ID, tableA.field2 from tableA
LEFT JOIN tableB on tableA.ID = tableB.ID
WHERE tableB.ID IS NULL;

This may not be the only way, but it works with these 2
tables.

 

[waldemar]

Did I do this the right way, if I wanted to display all fields of just one direction?:

SELECT * from tblA
LEFT JOIN tblB on tblA.ID = tblB.ID
WHERE tblB.ID IS NULL;

This displays no result here. I'm looking for

TABLEA.ID MINUS TABLEB.ID ...

 

[waldemar]

[Just for closing this Thread]
Nah, it was far simpler than that (for anybody interested):

SELECT * FROM [tblA] WHERE [ID] NOT IN (SELECT [ID] FROM [tblB]);

compares [tblA] and [tblB] and displays only those entries from [tblA] that DO NOT EXIST in [tblB].

The next step - to add those missing entries in [tblB] - would be:

INSERT INTO [tblB]
SELECT [tblA].[ID] AS [ID]
FROM [tblA]
WHERE [ID] NOT IN (SELECT [ID] FROM [tblB]);


Thansk for the help, everybody