row comparisons between two tables 2

[gatetec]

I have two tables (newroster A, oldroster B) with the same columns. If tbl newroster has any new records or any updated records compared to tbl oldroster, then I want to extract the entire records from table newroster so that which rows are new and updated. Please advise.

select A.*
from newroster A, oldroster B
where (B.employee != A.employee or
B.firstname != A.firstname or
B.middlename != A.middlename or
B.lastname != A.lastname or
B.addr1 != A.addr1 or
B.addr2 != A.addr2 or
B.city != A.city or
B.state != A.state or
B.zip != A.zip or
B.hmphonenbr != A.hmphonenbr or
B.datehired != A.datehired or
B.termdate != A.termdate or
B.department != A.department or
B.departmen2 != A.departmen2 or
B.jobcode != A.jobcode or
B.jobdescrip != A.jobdescrip or
B.empstatus != A.empstatus or
B.emergconta != A.emergconta or
B.emergphone != A.emergphone)

thx much

 

[Qik3Coder]

Select A.* from
New A
LEFT JOIN old B
On a.a = b.a
and a.b = b.b
and a.c = b.c

WHERE b.a is null --where b is a column that such as a primary key that you know will be there if the data matches.

-Sometimes the answer to your question is the hack that works

 

[gatetec]

tbl newroster has 4100 rows, and oldroster B has 4089 rows.
So, there will be a dozen of new records and a couple of dozens of updated records. The expected output from the query would 2~3 dozens of records, but the query below returns 4100 rows, which is the entire rows of tbl newroster . Pls advise.


Select A.* from
New A
LEFT JOIN old B
On (A.employee = B.employee AND
A.firstname = B.firstname AND
A.middlename = B.middlename AND
A.lastname = B.lastname AND
A.addr1 = B.addr1 AND
A.addr2 = B.addr2 AND
A.city = B.city AND
A.state = B.state AND
A.zip = B.zip AND
A.hmphonenbr = B.hmphonenbr AND
A.datehired = B.datehired AND
A.termdate = B.termdate AND
A.department = B.department AND
A.departmen2 = B.departmen2 AND
A.jobcode = B.jobcode AND
A.jobdescrip = B.jobdescrip AND
A.empstatus = B.empstatus AND
A.emergconta = B.emergconta AND
A.emergphone = B.emergphone)
where B.employee is null

 

[cmmrfrds]

Assuming employee is the primary key.

select A.*
from newroster A
left join oldroster B
on B.employee = A.employee
where
NOT (B.employee = A.employee and
B.firstname = A.firstname and
B.middlename = A.middlename and
B.lastname = A.lastname and
B.addr1 = A.addr1 and
B.addr2 = A.addr2 and
B.city = A.city and
B.state = A.state and
B.zip = A.zip and
B.hmphonenbr = A.hmphonenbr and
B.datehired = A.datehired and
B.termdate = A.termdate and
B.department = A.department and
B.departmen2 = A.departmen2 and
B.jobcode = A.jobcode and
B.jobdescrip = A.jobdescrip and
B.empstatus = A.empstatus and
B.emergconta = A.emergconta and
B.emergphone = A.emergphone)
OR b.employee is null

 

[Qik3Coder]

I had the tables backwards. You would start with the table that you know has data in it, and join onto the table that you are unsure of.
Negation comparisons are usually more expensive than direct comparisions.

-Sometimes the answer to your question is the hack that works

 

[gatetec]

thx so much.
it works great..