Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    19

    Unanswered: non-matching recrods 2 tables (Newbie)

    Hi,

    I am new to SQL and these forums, and have a quick simple question.

    I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.

    Here is an example of what the two tables contain.

    Table 1
    customer_no eaddress_no name address (36000 records)

    Table2
    customer_no eaddress_no email (17000 records)

    I need to find out which customers are not in table 2 by linking the eaddress_no numbers.


    This is one of the scripts i ran that gives me the large results with a ton of dups:

    SELECT *
    FROM T_EADDRESS inner JOIN
    T_CUST_LOGIN ON T_EADDRESS.eaddress_no
    != T_CUST_LOGIN.eaddress_no

    Thanks in advance for any help with this.

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Try this:

    SELECT Table1.*
    FROM Table1 INNER JOIN Table2
    ON Table1.eaddress_no <> Table2.eaddress_no

  3. #3
    Join Date
    Jan 2004
    Posts
    19
    I got the same results:

    Alot of dups.

    I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.

    SELECT A.No,A.Date
    FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    If your query does not work, try this:

    SELECT *
    FROM Table1
    WHERE eaddress_no NOT IN
    (SELECT eaddress_no FROM Table2)

  5. #5
    Join Date
    Jan 2004
    Posts
    19
    gyuan that worked.
    yours looked a little easier for me to follow so i just used that one.
    Thanks a million.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Using a left join is generally preferable to NOT EXISTS.

    SELECT Table1.*
    FROM Table1
    left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
    WHERE Table1.eaddress_no is null


    This can also be easily be modified to show records that are missing from either table:

    SELECT Table1.*, Table2.*
    FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
    WHERE Table1.eaddress_no is null or Table2.eaddress_no is null
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jan 2004
    Posts
    19
    Thanks Blind Man,

    Those make sense to me now . I helps to see the table names in the query.

  8. #8
    Join Date
    Jan 2004
    Posts
    19
    Blindman,

    you have an extra ) where would the opening one go?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just remove it...you don't need it....
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. No Parenthesis is necessary. It was a copy/paste error.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jan 2004
    Posts
    19
    I tried that and it returns no records.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you run this

    SELECT Table1.*
    FROM Table1
    left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
    WHERE Table1.eaddress_no is null

    and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.

    Run the second one with the FULL OUTER JOIN clause and see what you get:

    SELECT Table1.*, Table2.*
    FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
    WHERE Table1.eaddress_no is null or Table2.eaddress_no is null
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OOPS!
    WHERE clause was looking at wrong table! We want to see if Table2 is null...

    SELECT Table1.*
    FROM Table1
    left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
    WHERE Table2.eaddress_no is null
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jan 2004
    Posts
    19
    Yep that did it. And it took about 2 seconds few to run.

    Thanks blindman, now i see.

Posting Permissions

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