Querry to find records in one table but not in another

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

What will be the querry to

1. Find the records in "MoreData" but not in "LessData"
2. Find only those records in "MoreData" that are also
present in "LessData"

I did try querry wizard, tinkered with join properties
and got lost.

Following are the contents of table with name "MoreData":
AutoID Name Comment
1 One Comment1
2 Two Comment2
3 Three Comment3
4 Four Comment4
5 Five Comment5
6 Six Comment6
7 Seven Comment7
8 Eight Comment8
9 Nine Comment9
10 Ten Comment10


Following are the contents of table with name "LessData":
AutoID Name Comment
1 One Comment1
3 Three Comment3
4 Four Comment4
6 Six Comment6
7 Seven Comment7
8 Eight Comment8
10 Ten Comment10

Also please suggest some good reference material to learn
types of joins with example.

Please Help

Thanks
 
What will be the querry to

1. Find the records in "MoreData" but not in "LessData"
Click to expand...
2. Find only those records in "MoreData" that are also
present in "LessData"
Click to expand...

(2) is easy: the default Inner Join:

SELECT [AutoID], [Name], [Comment]
FROM MoreData INNER JOIN LessData
ON MoreData.AutoID = LessData.AutoID;

(1) requires some slightly twisted logic: a "frustrated outer join".
First change the join type in the previous query to Left Outer Join:
this will show ALL records in MoreData, whether or not they have a
match in LessData. The records which do not have a match will have
NULLS for all fields in LessData. You can then use this fact to
include ONLY those records with a NULL - i.e. the records with no
match:

SELECT [AutoID], [Name], [Comment]
FROM MoreData LEFT JOIN LessData
ON MoreData.AutoID = LessData.AutoID
WHERE LessData.AutoID IS NULL;

Books? Alas, Rebecca Riordan's definitive _Designing Relational
Database Applications_ is out of print...
 
Back
Top