At 10:21 AM 13/11/2003 +0200, you wrote:

>Hi all,
>
>I know that this is a simple question, and that I should know the answer
>... but I don't. *blush*
>
>Given two tables, how do I write a select that will show me all the records
>that are in Table1 but NOT in Table2?
>
>CREATE TABLE TABLE1
>(
>RECORDNO INTEGER NOT NULL,
>SEQUENCENUMBER INTEGER,
>PRIMARY KEY(RECORDNO)
>);
>
>CREATE TABLE TABLE2
>(
>RECORDNO INTEGER NOT NULL,
>SEQUENCENUMBER INTEGER,
>PRIMARY KEY(RECORDNO)
>);
>
>In this example, assume that I want to find all of the records in Table1
>where there is no matching record in Table2.

select table1.RECORDNO,
table1.SEQUENCENUMBER
from table1
where not exists (select table2.RECORDNO,
table2.SEQUENCENUMBER from table2
where table2.RECORDNO = table1.RECORDNO
and table2.SEQUENCENUMBER = table1.SEQUENCENUMBER);

or
select table1.RECORDNO,
table1.SEQUENCENUMBER,
table2.RECORDNO,
table2.SEQUENCENUMBER
from table1
left join table2 on
table2.RECORDNO = table1.RECORDNO
and table2.SEQUENCENUMBER = table1.SEQUENCENUMBER
where table2.recordno is null and table.SEQUENCENUMBER is null;

h.