Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Comparing Tables in SQL Server 2k

    I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.

    I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assuming that both columns are part of the primary key, i.e. not null

    rows in table1 that don't exist in table2 --
    Code:
    select t1.PartID
         , t1.RaceID
      from table1 as t1
    left outer
      join table2 as t2
        on t1.PartID = t2.PartID
       and t1.RaceID = t2.RaceID 
     where t2.PartID is null
    rows in table2 that don't exist in table1 --
    Code:
    select t2.PartID
         , t2.RaceID
      from table1 as t1
    right outer
      join table2 as t2
        on t1.PartID = t2.PartID
       and t1.RaceID = t2.RaceID 
     where t1.PartID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's okay, but I think it needs more cowbell:

    Code:
    select	table1.PartID,
    	table1.RaceID,
    	table2.PartID,
    	table2.RaceID
    from	table1
    	full outer join table2
    		on table1.PartID = table2.PartID
    		and table1.RaceID = table2.RaceID
    where	table1.PartID is null
    	or table2.PartID is null
    I just gotta have more cowbell!
    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
    cowbell? is that unique to sql server? i never heard of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2003
    Posts
    114
    Thanks. That allowed me to find the discrepancies. Does anyone know where I can find a tutorial on joins (inner, outer, right, left, etc)?

    Thanks!

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    BOL (Books online), the topic "Types of Joins".

  7. #7
    Join Date
    Apr 2003
    Posts
    114
    thanks for your help!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hilarious clip

    i don't understand the relevance to full outer joins, but that could just be me

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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    http://www.urbandictionary.com/defin...bell&r=s&pos=1

    Also: The addition of something which contributes nothing of value. ie: the full outer join as opposed to the separate left joins.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Dec 2004
    Location
    Switzerland
    Posts
    4

    What about a UNION?

    What about creating a UNION view consisting only of the two key attributes? SQL Server will prepare a nice QEP for quick selection; if you have a second view, that queries on NULL values for both fields in that view it will be faster than any outer join, maybe ;-)

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I got a fever! And the only prescription is MORE COWBELL!
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a UNION view!! why didn't i think of that!!
    Code:
    select PartID
         , RaceID
         , min(source) as source_table
      from (
           select PartID
                , RaceID
                , 'table1' as source 
             from table1
           union all
           select PartID
                , RaceID
                , 'table2' 
             from table2
           ) as u
    group
        by PartID
         , RaceID
    having count(*) between 0 and 1
    note how MIN() will tell you which of the tables the unmatched row comes from

    sweet, eh?

    the HAVING clause won't ever actually find count(*)=0, i just threw that in there because of the cowbells

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

  14. #14
    Join Date
    Apr 2003
    Posts
    114

    Cool

    And I thought I knew a little something about sql queries.....

    I guess that is accurate- a LITTLE something...

    This is a very informational and helpful thread!

    Thanks to all--although I am still a little in the dark re: this cowbell thing...

Posting Permissions

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