Re: SQL Challenge

Neill -

I'm an idiot...
I forgot the query to show the inserts, so I'll restate all FOUR of them in
one place:


Query 1 (Changed):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'U' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
(
B.FieldC <> A.FieldC or
B.FieldD <> A.FieldD or
B.FieldE <> A.FieldE
)
)


Query 2 (unchanged):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'X' from FileB B
Where Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
and
(
B.FieldC = A.FieldC AND
B.FieldD = A.FieldD AND
B.FieldE = A.FieldE
)
)


Query 3 (Deletes):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'D' from FileB B
Where NOT Exists
(
select *from fileA A
where (B.FieldA = A.FieldA and B.FieldB=A.FieldB)
)


Query 4 (Inserts):

Select FieldA,FieldB,FieldC, FieldD, FieldE, 'I' from FileA A
Where NOT Exists
(
select *from fileB B
where (A.FieldA = B.FieldA and A.FieldB=B.FieldB)
)


Neill wrote:
File A is a new version of File B, i.e. at one point it contained exactly
the same records as File B. However over time File A has had records
inserted, deleted and updated.



Can anybody think of a way using sql that gives me a third file FILE C, that
has the exact same format as FILE A and FILE B, except for one extra field
called Record Status. So that file C contains all of the original records
from FILE B that are no longer in FILE A (where record status = D, Deleted),
all of the new records in FILE A (where record status = I, Inserted) , all
of the Records that are in FILE A that have been changed from FILE B (where
record status = U, Updated) and finally all records in FILEA that have not
changed from FILE B( where status = X, unchanged)