find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--