var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: How do you compare the data in two tables
I have two tables and I want to know if every record from the first table is in the second one and if its data mathes exactly?
Any suggestion for a short way to do this?
Do you have a non-surrogate primary key for these tables? If so, you could use:
If there is not a non-surrogate primary key, you can do the following, which could take a long time:
select * from tablea A
join tableb B
on A.PK = A.PK
where checksum(A.*) <> checksum (B.*)
Please note that especially for the second approach there is a very small chance that two different rows could have the same checksum, since this is actually a finite function.
select * from tablea
where checksum(*) not in (select checksum(*)
from tableb )
Last edited by cascred; 09-29-05 at
Hopefully, if the table does not have a surrogate key, it will have a simple or composite natural key you can join on. But the checksum method works quickly if you can stand a very small chance of an error. Use Binary_Checksum() if you need case sensitivity.
venetai, didn't you like the answer i gave in the mysql forum?
guys, this is probably homework
same poster, same question, different dbms forums? with time lapse between?
homework, i tellya
especially since somebody else also asked this question last week
thankfully, cascred's obfuscatory answer is very much apropos
non-surrogate primary key -- that's a good one!!
this is not homework...
r937 what is the problem? Yeah, I posted that question and then I realized that it was the wrong forum...
And no, your answer did not help me...I need something more specific...(had no time to search for the answer..)
And no, this is not homework...It has been years since I was in school...
And isn't the goal of this forum to help people?!
Thank you to everybody who replied, including you. After all, you posted the reply in the other forum.
I like to assume that a poster has made an honest effort to do a search before posting a question. It only takes a few seconds to type in a few keywords and do a search. Sometiimes you get lucky the first time.
Originally Posted by
It usually takes us more time to post a solution than it would take you to do some quick research.
I wouldn't have taken sides, but you blew it with the admission above.
I did not mean that
By saying that I do not have time to search I meant something very different...
Yeah, I searched for that posted question and I did not find it...
Then, I tried to search and find out how to use full outer join and then an equi-join to solve my problem (I'm sorry I'm not a DB developer and I have to search for simple things like that)...
I did not come to a good solution and that's why I posted the question again - but this time in the correct forum...
Was I wrong when I got upset because someone posted a message saying something like "do not answer because the user is cheating..this is homework.."?!
I did not cheat...I posted that question because I was confused and I could not get to the solution...
And by the way, cascred, it was your post that helped me find the solution of my situation, for which I am very thankful.
I hope this explain things a little bit. I really did not mean to waste people's time...
then you could have said so, rather than just abandoning that thread and starting a new one somewhere else
Originally Posted by
i do hope you understand why we frown on helping people with homework
and i also hope you realize that your question did sound a lot like homework
as brett would say, if it walks like a duck, and talks like a duck, chances are it's a duck
I thought I said
"If it walks like a duck, sounds like a duck, then it must be fowl."
i said "would say" not "did say"
homework and mister ivory tower are two different ducks
I'll have to read you more literally....
And yes, they are light years apart...kinda makes me want to do homework.