Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2005
    Posts
    24

    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?

    Thank you!

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Do you have a non-surrogate primary key for these tables? If so, you could use:

    Code:
    select * from tablea A
    join tableb B
       on A.PK = A.PK
    where checksum(A.*) <> checksum (B.*)
    If there is not a non-surrogate primary key, you can do the following, which could take a long time:

    Code:
    select * from tablea
    where checksum(*) not in (select checksum(*)
                                              from tableb    )
    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.
    Last edited by cascred; 09-29-05 at 17:59.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    venetai, didn't you like the answer i gave in the mysql forum?

    http://www.dbforums.com/t1197386.html

    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2005
    Posts
    24

    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?!

    Thanks anyway...

    Thank you to everybody who replied, including you. After all, you posted the reply in the other forum.

    J

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by venetai
    ...(had no time to search for the answer..)

    J
    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.

    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.

    Bill

  7. #7
    Join Date
    Apr 2005
    Posts
    24

    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...

    Thank you.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by venetai
    And no, your answer did not help me...
    then you could have said so, rather than just abandoning that thread and starting a new one somewhere else

    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thought I said

    "If it walks like a duck, sounds like a duck, then it must be fowl."

    http://weblogs.sqlteam.com/brettk/ar...8/11/7516.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i said "would say" not "did say"

    homework and mister ivory tower are two different ducks

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'll have to read you more literally....

    And yes, they are light years apart...kinda makes me want to do homework.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •