SQL - compare data between two tables

Posted on 2011-04-22
Last Modified: 2012-05-11
I have two tables that contain the same fields: LoanNumber, LoanAmount

I need a query that gives me all records where the LoanNumber matches, but the LoanAmount is different.

What's wrong with my code below? Thanks!
select LoanNumber, loanamount
from tableA
where (tableA.LoanNumber in 
             (select loannumber from tableB) 
              and tableA.loanamount <> tableB.loanamount)

Open in new window

Question by:asmyatt
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    Select loannumber, loanamount, (select loanamount from table b) as loanamountb from tablea
    Where loanamount <> loanamountb

    This will only work if table b doesn't have loan numbers that are not in tablea
    LVL 38

    Accepted Solution

    Select loannumber, loanamount, (select loanamount from tableb where loannumber=tablea.loannumber) as loanamountb from tablea
    Where loanamount <> loanamountb
    LVL 22

    Expert Comment

    No offense intended but that is really not that good of a query.

    SELECT A.loannumber
      ,A.loanamount AS LoanAmountA
      ,B..loannumber AS LoanAmountB
    FROM tablea A
    INNER JOIN tableb B
    ON  A.loannumber = B.Lloannumber
    WHERE A.loanamount <> B.loanamountb;

    Open in new window


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now