Exclusion query

[beanbrain]

I know this is really simple and probably a no-brainer, but it seems my brain is on holiday today.

I want to create a query that will compare two tables of similar (same) structures and return the records from the second table that do not exist in the first table.

This can be limited to the first field of both tables.

 

[Chesterex1]

Start a New Query - then select Find Unmatched Query wizard - the wizard will talk you through constructing your query.
Thanks
Tim

 

[mtanney]

The below SQL should work fine.

select t1.fieldname from "tablename1" t1
where t1.fieldname not IN
(select t2.fieldname from "tablename2" t2 )

 

[beanbrain]

Actually I found this elsewhere but I thought I'd post it anyway. I knew that joins where used but I was trying to place the not operator in front of the join example. Turns out count=0 in the compare to table is the way to go along with the inclusion operator in the compare from table.

The query below will return the awards missing in the :build:Award.db table as compared to the :backp:Award.db table.

To wit:

[tt]
Query
ANSWER: RIV:ANSWER.DB

:build:award.DB | AwardNo |
| Check _award! |

:backp:Award.DB | AwardNo |
| _award, count=0 |

EndQuery
[/tt]