No duplicates

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

I know that I should know this, but I am having a complete dumb moment.

Here is a simple example.

I have two tables. 1st table has red, blue, green, yellow, orange
2nd table has red, green, orange

I want to do a query that returns blue and yellow

I want to know out of the first table what is not in the second table.
 
You need what is called an Outer Join (Left or Right). Join the two
tables based on the field "Colour" and you will get an "Inner Join".
Now double click on the join and you will be shown the Join Properties
screen. Choose "Include all records from the table with the most
colours and only those from the table with the least colours where they
are equal" (or something like that). Then you want to add the Colour
field from each of the two tables, but for criteria for the table with
the least colours you want to put "Is Null"

(Boy, it sounds convoluted to do it that way)

Or if you know how to input SQL you can use this:
SELECT Table1.colour
FROM Table1 LEFT JOIN Table2 ON Table1.colour = Table2.colour
WHERE Table2.colour Is Null;

Table1:
colour - Text - Primary Key

Table1 Values:
Red
Yellow
Blue
Green
Orange

Table2:
colour - Text - Primary Key

Table2 Values:
Red
Green
Orange

Query1:
SELECT Table1.colour
FROM Table1 LEFT JOIN Table2 ON Table1.colour = Table2.colour
WHERE Table2.colour Is Null;

Query1 Results:
Blue
Yellow

Cheers,
Jason Lepack
 
I want to know out of the first table what is not in the second table.
Click to expand...

select thing
from first
where thing NOT IN
( select thing from second )
order by thing

Hope that helps


Tim F
 
Back
Top