Outer join confussion

  • Thread starter Thread starter chaddy23
  • Start date Start date
C

chaddy23

I have 2 tables where I need to find what is not in another table. Table
values include

Table 1
Office #;State
1;WI
1;AZ
1;IL
2;WI
2;FL

TAble 2
Office #;State
1;WI
1;AZ
2;WI
2;FL

So what I need is to find Office # and State which is not matching in Table 1.
In the example I should get 1 record back i.e. 1;IL
I am pulling my hair out trying to figure out why I cannot get this to work.
I have to basically not match on the combination of the Office # and State.
We are trying to clean up records where they dont match.
 
If everything that is in table 2 is always in table 1 then all you need
is a left join on each field and to select only the records where table
2 is null.

SELECT T1.*
FROM Table1 as T1 LEFT JOIN Table2 as T2 ON T1.[Office#] = T2.[Office#]
AND T1.State = T2.State
WHERE T2.[Office#] Is Null;

You can create a LEFT or RIGHT JOIN by right clicking on a Join in the
query builder and selecting "Join Properties". In this case I selected
"Include all records from T1 and only those records from T2 where the
joined fields are equal."

This means that every record will get returned from T1 and from T2 it
will be only the records that match.

Now you will notice that if you put all 4 fields in you will see
records where T2 doesn't have values, these are the ones that you want
to return, so for your criteria I chose T2.[Office#] is Null.

Cheers,
Jason Lepack
 
chaddy23 said:
I have 2 tables where I need to find what is not in another table. Table
values include

Table 1
Office #;State
1;WI
1;AZ
1;IL
2;WI
2;FL

TAble 2
Office #;State
1;WI
1;AZ
2;WI
2;FL

So what I need is to find Office # and State which is not matching in Table 1.
In the example I should get 1 record back i.e. 1;IL
I am pulling my hair out trying to figure out why I cannot get this to work.
I have to basically not match on the combination of the Office # and State.
We are trying to clean up records where they dont match.
Click to expand...


Use the Find Unmatched Query wizard.
 
Back
Top